데이터를 SQL Server로 가져오기 위한 여러 방법들

원본 : http://sqlserverblogs.blogspot.com/2007/04/sharing-knowledge-import-and-export.html

 

데이터를 SQL Server로 옮기는 작업은 대부분의 DBA나 개발자가 일상적으로 접하는 업무입니다. 가장 단순한 방법으로 가져오기/내보내기 마법사를 이용하거나 SQL 2000의 DTS나 SQL 2005의 SSIS를 이용할 수도 있습니다. 본 글에서는 이러한 방법 외에 수행할 수 있는 다양한 방법들에 대해 살펴 보겠습니다.

 

BCP

가장 널리 사용되는 명령입니다. BCP 명령은 데이터를 입력(Import)하거나 출력(Export)하는데 모두 사용할 수 있지만, 텍스트 형태의 데이터에 대해서만 사용할 수 있습니다. 윈도우 명령 프롬프트에서 수행하며, SQL의 쿼리 분석기나 저장 프로시저에서 xp_cmdshell 명령을 이용해서 호출하거나 DTS, SSIS 내에서 호출하는 방식으로 이용할 수 있습니다.

다음은 C:\ImportData.txt를 dbo.ImportTest 테이블로 옮기는 예 입니다.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

(BCP 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

 

BULK INSERT

BULK INSERT문은 T-SQL을 이용해서 직접 데이터를 SQL Server의 테이블로 저장할 수 있도록 하는 명령입니다.

다음은 C:\ImportData.txt 데이터 파일을 dbo.ImportTest 테이블로 옮기는 예 입니다.

BULK INSERT dbo.ImportTest FROM 'C:\ImportData.txt' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

(BULK INSERT 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

OPENROWSET

이 명령어는 다른 데이터 소스로부터 직접 쿼리를 수행할 수 있도록 하는 T-SQL 명령입니다. INSERT INTO 명령과 같이 사용함으로써 외부의 데이터를 SQL Server의 테이블로 저장할 수 있습니다.

다음 예제는 C:\InportData.xls 엑셀 파일의 [Sheet1$] 시트에 있는 데이터를 dbo.ImportTest 테이블로 옮기는 예 입니다.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

 

[Sheet1$] 시트 명을 직접 지정하는 대신 다음과 같이 SELECT * FROM 명령을 이용해서 데이터를 읽어오도록 할 수도 있습니다. 이 방법을 이용하면 데이터를 읽어올 때 원하는 데이터만을 읽어오거나 열을 가공해서 읽어올 수 있습니다.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

(편집자 주 : 엑셀에서 데이터를 읽어오는 SQL은 일반 SQL Server에서 사용하는 SQL문과 비슷하지만, 일부 명령에서 차이가 있습니다. 다음 링크의 글을 참고하시기 바랍니다.)

 

(BULK INSERT 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

OPENDATASOURCE

OPENROWSET과 비슷한 방식으로 수행되며, INSERT INTO 명령과 같이 사용함으로써 데이터를 테이블로 저장할 수 있습니다.

INSERT INTO dbo.ImportTest SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

(OPENDATASOURCE 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

OPENQUERY

OPENQUERY 명령을 이용해서 데이터를 가져오기 위해서는 두 단계가 필요합니다. 우선 연결된 서버(Linked Server)를 구성해야 하며, 구성된 서버 정보를 이용해서 데이터를 읽어와야 합니다.

EXEC sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\ImportData.xls', NULL, 'Excel 8.0' GO   INSERT INTO dbo.ImportTest SELECT * FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

(OPENQUERY 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

Linked Server

연결된 서버(Linked Server)를 이용하는 것 역시 외부 데이터를 읽어오는 방법 중 하나입니다. OPENQUERY 에서와 같이 연결된 서버를 구성한 후, SQL 문에서 직접 가져오는 방식입니다.

 

EXEC sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\ImportData.xls', NULL, 'Excel 8.0' GO   INSERT INTO dbo.ImportTest SELECT * FROM ImportData...Sheet1$

(Linked Server 명령에 대해 보다 자세한 것을 알고 싶으면 다음 링크의 도움말을 참고하시기 바랍니다.)

 

 

 

하나의 데이터를 가져오는 데에도 여러 방법이 있습니다. 어떤 것이 절대적으로 가장 우수하고 어떤 것이 나쁜 것은 없습니다. 항상 나쁜 것이었으면 버전이 7.0, 2000, 2005로 발전되면서 제거되었을 것입니다.  OPENROWSET이나 OPENDATASOURCE가 OPENQUERY에 비해 연결된 서버를 구성하는 단계가 없기 때문에 간단할 수는 있으나 가져올 파일명이나 암호 등이 노출되어야 하는 단점이 있습니다. 만약 Linked Server가 구성된 곳이라면 간단히 OPENQUERY나 Linked Server를 이용하는 것이 더 좋을 것입니다.  SQL Agent가 설치되지 않은 곳에서 Windows 예약 작업만으로 명령을 수행하기 위해서는 BCP 명령을 사용하는 것이 최적일 것입니다.  이와 같이 다양한 상황에 따라 수행할 수 있는 명령들이 있기 때문에 위에서 설명한 다양한 방법들을 미리 익혀 놓을 필요가 있을 것이라 생각되어 블로그의 글을 간단히 번역, 정리하였습니다.

 

 

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.