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

 



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 36283
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 18894
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1553
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1138
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 1003
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3268
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1694
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1442
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1139
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 1002
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 913
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 974
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 1127
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 2016
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1321
» SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1359
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2824
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1779
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 2068
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5935
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1426
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 4477





XE Login