SQL Server로 데이터 가져오기

SQL Server로 데이터 가져오기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

DBA로 일하면서 많은 하는 작업 중 하나가 데이터를 이동하는 일이다. 데이터를 이동하는 방법에는 여려가지 방법이 있다. 가장 직관적인 방법은 방법은 마법사를 이용한 [가져오기/내보내기]이다. 하지만 이외에 여러가지 옵션이 있다. 예를 들어 엑셀이나 텍스트 문서의 파일 데이터를 가져오는 경우도 있다.(아마도 파일을 가져오는 작업이 더 많을 듯 하다.) 이런 작업이 계속 반복 될 때 매번 마법사를 통하기에는 많은 불편함이 있다. 또한 로컬이 아닌 위치에 있을 때는 어떻게 할까?

다양한 옵션을 통해서 데이터를 가져오는 방법을 알아 보자.

 

실습에 사용할 텍스트 파일을 생성.

 

실습용 테이블 생성.

BEGIN TRY     DROP TABLE PERSON_ADDRESS END TRY BEGIN CATCH END CATCH GO   CREATE TABLE PERSON_ADDRESS ( ADDRESSID INT, ROWGUID NVARCHAR(500) )

 

 

[SSIS]

SSIS 강좌 참고 : http://sqlmvp.kr/140133135633

 

 

[BCP]

가장 많이 사용되고 있는 방법 중 하나이다. 가져오기 내보내기 모두 가능하며 주로 텍스트 형식에 많이 사용한다. 윈도우 명령 프롬프트, xp_cmdshell, ssis에서 모두 사용이 가능하다.

bcp SW_TEST.dbo.Person_Address in c:\test.txt -T -c

 

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms162802.aspx

 

 

[BULK INSERT]

T-SQL을 사용하여 SQL 서버에서 직접 데이터를 가져오기 할 수 있다.

BULK INSERT dbo.PERSON_ADDRESS FROM 'C:\TEST.TXT'

 

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms188365.aspx

 

 

[OPENROWSET]

이 명령은 SQL Server에서 직접 다른 데이터 소스에서 데이터를 쿼리 할 수 있는 명령어다. INSERT 문과 함께 명령어를 사용하여 다른 데이터의 소스를 SQL Server로 삽입 할 수 있다.

실습 스크립트는 엑셀파일을 로드하는 테스트를 하였으며 시트1의 값을 가져온다.

INSERT INTO DBO.PERSON_ADDRESS --SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', [Sheet1$]) SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\test.xls', [Sheet1$])

 

MSDN의 예제에 따라 [Microsoft.Jet.OLEDB.4.0]을 사용하였으나 계속해서 에러 발생. 확인 결과 64비트의 환경에서는 다른 드라이버를 사용해야 한다고 한다.

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms190312.aspx

 

 

실습 테스트 환경이 64bit여서 [Microsoft.ACE.OLEDB.12.0]을 사용하였다. 정상적으로 동작 되는 것을 확인 할 수 있다.

 

 

[OPENDATASOURCE]

SQL Server에서 다른 데이터 소스에 쿼리 할 수 있으며 OPENROWSET와 유사하다.

--SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Test.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ; SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Test.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

 

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms179856.aspx

 

 

[OPENQUERY]

먼저 연결된 서버 설정(링크드 서버)이후 OPENQUERY 명령을 사용한다. 열 및 행의 필터가 가능하다.

exec sp_addlinkedserver 'ImportData', 'ACE 12.0', 'Microsoft.ACE.OLEDB.12.0', 'C:\test.xls', NULL, 'Excel 8.0' --exec sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.4.0', 'C:\test.xls', NULL, 'Excel 8.0' SELECT * FROM OPENQUERY (ImportData, 'SELECT * FROM [Sheet1$]')

 

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms188427.aspx

 

 

[Linked Servers]

exec sp_addlinkedserver 'ImportData', 'ACE 12.0', 'Microsoft.ACE.OLEDB.12.0', 'C:\test.xls', NULL, 'Excel 8.0' select * from ImportData...Sheet1$

 

 

참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms190479.aspx

 

 

지금까지 설명한 방법 외에도 많은 방법이 있으니 사용 방법을 잘 습득하여 활용 할 수 있도록 하자.

 

참고 링크 :

http://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/#comments

 

 

2013-01-31 / 강성욱 / http://sqlmvp.kr