데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
» SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login