SQL Server 대량 데이터(엑셀 등) 가져오기 및 내보내기

MSDN의 SQL학습을 학습하기위해서 정리를 해나갑니다.   MicrosoftSQL Server에서는 SQL Server 테이블과 데이터 파일 사이에 대량으로 데이터(대량 데이터)를 가져오고 내보낼 수 있습니다. 이 기능은 SQL Server와 다른 데이터 원본 간에 데이터를 효과적으로 전송하는 데 필수적입니다. 대량 내보내기는 SQL Server 테이블의 데이터를 데이터 파일로 복사하는 것입니다. 대량 가져오기는 데이터 파일에서 SQL Server 테이블로 데이터를 로드하는 것입니다. 예를 들어 Microsoft Excel 응용 프로그램의 데이터를 데이터 파일로 내보낸 다음 해당 데이터를 SQL Server 테이블에 대량으로 가져올 수 있습니다.   대량 가져오기 및 내보내기 작업 정보 데이터 대량 가져오기 및 내보내기에 사용할 수 있는 여러 방법을 나열하고 간단하게 비교합니다. In-process와 Out-of-process 작업을 비교하고 서식 파일에 대해 설명하며 쿼리 프로세서와 대량 가져오기 작업에 대한 정보를 제공하고 성능 고려 사항을 제공합니다.   <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none" class=txc-table border=0 cellSpacing=0 cellPadding=0 width=700> <TBODY> <TR> <TD style="BORDER-BOTTOM: #000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000 1px solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 25%; COLOR: #000; BORDER-TOP: #000 1px solid; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">방법</TD> <TD style="BORDER-BOTTOM: #000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #e6e6e6; WIDTH: 25%; COLOR: #000; BORDER-TOP: #000 1px solid; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">설명 </TD> <TD style="BORDER-BOTTOM: #000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #e6e6e6; WIDTH: 25%; COLOR: #000; BORDER-TOP: #000 1px solid; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> 데이터 가져오기</TD> <TD style="BORDER-BOTTOM: #000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #e6e6e6; WIDTH: 25%; COLOR: #000; BORDER-TOP: #000 1px solid; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">데이터 내보내기 </TD></TR> <TR> <TD style="BORDER-BOTTOM: #000 1px solid; BORDER-LEFT: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> bcp 유틸리티</TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> 데이터를 대량으로 내보내고 가져오며 서식 파일을 생성하는 명령줄 유틸리티(Bcp.exe)</TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">예   </TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> 예</TD></TR> <TR> <TD style="BORDER-BOTTOM: #000 1px solid; BORDER-LEFT: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> BULK INSERT 문</TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">데이터 파일에서 데이터베이스 ㅌ이블이나 분할되지 않은 뷰로 직접 데이터를 가져오는 Transact-SQL 문입니다. </TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> 예</TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">아니오 </TD></TR> <TR> <TD style="BORDER-BOTTOM: #000 1px solid; BORDER-LEFT: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> INSERT ... SELECT * FROM OPENROWSET(BULK...) 문 </TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> INSERT 문의 데이터를 선택하는 OPENROWSET(BULK) 함수를 지정하여 대량의 데이터를 sql server 테이블로 가져오기 위해 OPENROWSET 대량 행 집합 공급자를 사용하는  Transact-SQL 문입니다.</TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1">예     </TD> <TD style="BORDER-BOTTOM: #000 1px solid; BACKGROUND-COLOR: transparent; WIDTH: 25%; COLOR: #000; BORDER-RIGHT: #000 1px solid" cols="1" rows="1"> 아니오</TD></TR></TBODY></TABLE>    제한 사항 SQL Server 대량 가져오기 작업에서는 CSV(쉼표로 구분된 값<Comma Separated Value>) 파일의 데이터를 가져오도록 지원하지 않습니다. 그러나 32비트 시스템에서 OPENROWSET을 Jet용 OLE DB 공급자와 함께 사용하면 대량 가져오기 최적화를 수행하지 않아도 CSV 데이터를 SQL Server 테이블로 가져올 수 있습니다. Jet에서는 schema.ini 파일에 정의된 스키마를 사용하여 텍스트 파일을 테이블로 처리합니다. schema.ini 파일은 데이터 원본과 동일한 디렉터리에 있습니다. CSV 데이터의 경우 schema.ini 파일의 매개 변수 중 하나는 "FORMAT=CSVDelimited"입니다. 이 방법을 사용하려면 Jet Test IISAMm의 작동 방식, 즉 연결 문자열 구문, schema.ini 사용법, 레지스트리 설정 옵션 등을 이해해야 합니다. 이에 대한 가장 유용한 정보는 Microsoft Access 도움말 및 KB(기술 자료) 문서에서 제공합니다. 자세한 내용은  <A href="http://go.microsoft.com/fwlink/?LinkId=128503">텍스트 데이터 원본 드라이버 초기화</A>, <A href="http://go.microsoft.com/fwlink/?LinkId=128504">보안된 Access 데이터베이스에 연결된 서버에서 SQL Server 7.0 분산된 쿼리 사용 방법</A>, <A href="http://go.microsoft.com/fwlink/?LinkId=128505">방법: Jet OLE DB 공급자 4.0을 사용하여 ISAM 데이터베이스 연결</A> 및 <A href="http://go.microsoft.com/fwlink/?LinkId=128501">Jet 공급자와 텍스트 IIsam을 사용하여 구분된 텍스트 파일 연결 방법</A>을 참조하십시오.      In-Process와 Out-of-Process 작업 BULK INSERT 문 및 OPENROWSET(BULK) 함수는 SQL Server에서 in-process를 실행하며 동일한 메모리 주소 공간을 공유합니다. 데이터 파일은 SQL Server 프로세스에서 열리므로 데이터는 클라이언트 프로세스와 SQL Server 프로세스 간에 복사되지 않습니다. BULK INSERT 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...)을 사용하여 데이터를 가져올 때 고려해야 할 보안 사항은 <A href="http://msdn.microsoft.com/ko-kr/library/ms175915.aspx">BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기</A>를 참조하십시오. 이와는 반대로 bcp 유틸리티는 out-of-process를 실행합니다. 프로세스 메모리 공간에서 데이터를 이동하려면 bcp가 프로세스 간 데이터 마샬링을 사용해야 합니다. 프로세스 간 데이터 마샬링은 메서드 호출 매개 변수를 바이트 스트림으로 변환하는 프로세스입니다. 이 작업을 수행하면 프로세서에 대량의 부하가 추가될 수 있습니다. 그러나 bcp는 클라이언트 프로세스에서 데이터를 구문 분석하여 기본 저장소 형식으로 변환하기 때문에 SQL Server 프로세스에서 구문 분석 및 데이터 변환을 오프로드할 수 있습니다. 따라서 CPU 제약 조건이 있는 경우 BULK INSERT 또는 INSERT ...SELECT * FROM OPENROWSET(BULK)를 사용하는 대신 bcp를 사용하여 둘 이상의 CPU가 있는 컴퓨터나 서로 다른 컴퓨터에서의 대량 가져오기의 성능을 향상시킬 수 있습니다.      서식 파일 bcp 유틸리티, BULK INSERT 및 INSERT ... SELECT * FROM OPENROWSET(BULK...)은 데이터 파일의 각 필드에 대한 서식 정보를 저장하는 서식 파일이라는 특수 파일을 사용하도록 지원합니다. 또한 서식 파일에는 해당 SQL Server 테이블에 대한 정보가 포함되어 있습니다. 서식 파일을 사용하여 SQL Server 인스턴스로 데이터를 대량으로 내보내거나 SQL Server 인스턴스에서 데이터를 대량으로 가져올 때 필요한 모든 서식 정보를 제공할 수 있습니다. 서식 파일을 사용하면 가져오기 작업 중 데이터 파일에 있는 데이터의 해석뿐만 아니라 내보내기 작업 중 데이터 파일에 있는 데이터의 서식을 지정할 수 있습니다. 이와 같이 융통성이 있기 때문에 SQL Server 또는 외부 응용 프로그램에 대한 특정 요구 사항에 따라 데이터를 해석하거나 데이터의 서식을 다시 지정하기 위해 특수한 목적의 코드를 작성할 필요가 없습니다. 예를 들어 대량으로 내보낸 데이터를 쉼표로 값을 구분해야 하는 응용 프로그램으로 로드해야 할 경우 서식 파일을 사용하여 내보낸 데이터에서 쉼표를 필드 종결자로 삽입할 수 있습니다. SQL Server 2005 이상 버전은 XML 서식 파일 및 비 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 SQL Server의 이전 버전에서 지원됩니다. XML 서식 파일은 SQL Server 2005의 새로운 기능입니다. 서식 파일을 생성할 수 있는 유일한 도구는 bcp 유틸리티입니다. 자세한 내용은 서식 파일 만들기를 참조하십시오. 서식 파일에 대한 자세한 내용은 데이터를 가져오거나 내보내기 위한 서식 파일을 참조하십시오.  참고  대량 내보내기 또는 가져오기 작업 동안 서식 파일이 제공되지 않는 경우 사용자는 명령줄에서 기본 서식 지정을 무시하도록 선택할 수 있습니다.      쿼리 프로세서 및 대량 가져오기 SQL Server의 인스턴스로 데이터를 대량으로 가져오려면 bcp 유틸리티, BULK INSERT 문 및 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문이 쿼리 프로세서와 함께 작동합니다. 3가지 방법을 모두 사용하여 OLE DB 행 집합으로 데이터 파일의 데이터를 변환할 수 있지만 변환 방법은 다음과 같이 서로 다릅니다. <OL style="LIST-STYLE-TYPE: decimal"> <LI>bcp 유틸리티는 데이터 파일을 읽고, 데이터를 OLE DB 행 집합으로 변환하는 SQL Server의 BCP(대량 복사 프로그램) API로 TDS 스트림을 보냅니다. </LI> <LI>BULK INSERT 및 OPENROWSET 대량 행 집합 공급자는 모두 파일 데이터를 OLE DB 행 집합으로 직접 변환합니다. </LI></OL> OLE DB 행 집합은 각 작업을 계획하고 최적화하는 쿼리 프로세서를 통해 대상 테이블에 삽입됩니다.        성능 고려 사항 성능 고려 사항은 대량의 데이터를 가져올 때도 중요합니다. 어떤 경우에는 대량 가져오기나 대량 내보내기 작업에서 다음 중 하나 이상의 작업을 처리하는 방법을 변경하여 성능을 향상시킬 수 있습니다. <OL style="LIST-STYLE-TYPE: decimal"> <LI>일괄 처리 스위치</LI> <LI>CHECK 제약 조건의 제약 조건 검사</LI> <LI>대량 트랜잭션이 기록되는 방식. 이는 일반적으로 전체 복구 모델을 사용하는 데이터베이스에 관련된 것입니다.</LI> <LI>내보낸 데이터 순서 지정</LI> <LI>병렬 데이터 가져오기</LI> <LI>테이블 잠금</LI> <LI>트리거 실행</LI></OL> 자세한 내용은 대량 가져오기 성능 최적화를 참조하십시오.  참고  대량 내보내기 작업에 사용할 수 있는 특별한 최적화 기술은 없습니다. 이러한 작업은 단순히 SELECT 문을 사용하여 원본 테이블에서 데이터를 선택합니다.        데이터 대량 가져오기에 대한 기본 지침 bcp 명령, BULK INSERT 문 또는 OPENROWSET(BULK) 함수를 사용하여 데이터 파일의 데이터만 대량으로 가져올 수 있습니다.  참고  텍스트 파일이 아닌 개체의 데이터를 대량으로 가져오는 사용자 지정 응용 프로그램을 작성할 수 있습니다. 메모리 버퍼에서 데이터를 대량으로 가져오려면 SQL Server Native Client(ODBC) API(응용 프로그램 프로그래밍 인터페이스) 또는 OLE DB IRowsetFastLoad 인터페이스에 bcp 확장을 사용합니다. C# 데이터 테이블에서 데이터를 대량으로 가져오려면 ADO.NET 대량 복사 API인 SqlBulkCopy를 사용합니다.     참고  원격 테이블로 데이터를 대량으로 가져올 수 없습니다.   데이터 파일에서 MicrosoftSQL Server 인스턴스로 데이터를 대량으로 가져올 경우 다음 지침을 따르십시오. <UL style="LIST-STYLE-TYPE: disc"> <LI>사용자 계정에 대해 필요한 권한 가져오기</LI></UL> bcp 유틸리티, BULK INSERT 문, 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문을 사용하는 사용자 계정에는 테이블에 대해 필요한 권한(테이블 소유자가 할당)이 있어야 합니다. 각 메서드에 필요한 권한에 대한 자세한 내용은 bcp 유틸리티, OPENROWSET(Transact-SQL) 및 BULK INSERT(Transact-SQL)를 참조하십시오.   <UL style="LIST-STYLE-TYPE: disc"> <LI>대량 로그 복구 모델 사용</LI></UL> 이 지침은 전체 복구 모델을 사용하는 데이터베이스에 대한 내용입니다. 대량 로그 복구 모델은 인덱싱되지 않은 테이블(힙)로 대량 작업을 수행할 때 유용합니다. 대량 로그 복구는 로그 행 삽입을 수행하지 않으므로 대량 로그 복구를 사용하면 트랜잭션 로그의 공간 부족 문제를 방지할 수 있습니다. 대량 로그 복구 모델에 대한 자세한 내용은 대량 로그 복구 모델에서의 백업을 참조하십시오. 대량 가져오기 작업을 수행하기 바로 전에 대량 로그 복구 모델을 사용하여 데이터베이스를 변경하는 것이 좋습니다. 변경한 직후에는 데이터베이스를 전체 복구 모델로 다시 설정해야 합니다. 자세한 내용은 전체 또는 대량 로그 복구 모델에서 전환을 참조하십시오.  참고  대량 가져오기 작업 동안의 로깅을 최소화하는 방법은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하십시오.    <UL style="LIST-STYLE-TYPE: disc"> <LI>데이터를 대량으로 가져온 후 백업</LI></UL> 단순 복구 모델을 사용하는 데이터베이스의 경우 대량 가져오기 작업이 완료된 후 전체 또는 차등 백업을 수행하는 것이 좋습니다. 자세한 내용은 SQL Server 데이터베이스의 전체 및 차등 백업 만들기를 참조하십시오. 대량 로그 복구 모델 또는 전체 복구 모델의 경우에는 로그 백업으로 충분합니다. 자세한 내용은 트랜잭션 로그 백업 만들기를 참조하십시오. 대규모 대량 가져오기의 성능 향상을 위해 테이블 인덱스 삭제 이 지침은 이미 테이블에 있는 데이터에 비해 많은 양의 데이터를 가져오는 경우에 대한 내용입니다. 이 경우 대량 가져오기 작업을 수행하기 전에 테이블의 인덱스를 삭제하면 성능이 매우 향상됩니다.  참고  이미 테이블에 있는 데이터에 비해 적은 양의 데이터를 로드하는 경우에는 인덱스를 삭제하면 성능이 저하됩니다. 인덱스를 다시 작성하는 데 필요한 시간이 대량 가져오기 작업 동안 절약된 시간보다 길 수도 있습니다.  자세한 내용은 대량 가져오기 성능 최적화를 참조하십시오. <UL style="LIST-STYLE-TYPE: disc"> <LI>데이터 파일의 숨겨진 문자 찾기 및 제거 </LI></UL>주로 데이터 파일의 끝 부분에 있는 숨겨진 문자는 다양한 유틸리티와 텍스트 편집기를 사용하여 표시할 수 있습니다. 대량 가져오기 작업 동안 ASCII 데이터 파일에 있는 숨겨진 문자가 문제를 일으켜 "예기치 않은 Null 발견" 오류가 발생할 수 있습니다. 이 경우 숨겨진 문자를 모두 찾아서 제거하면 이 문제가 해결됩니다.       bcp 유틸리티를 사용하여 대량 데이터 가져오기 및 내보내기 이 항목에서는 분할된 뷰를 포함하여 SELECT 문이 작동하는 SQL Server 데이터베이스의 어디에서나 데이터를 가져올 수 있도록 bcp 유틸리티를 사용하는 방법에 대해 간략하게 설명합니다. bcp 유틸리티(Bcp.exe)는 BCP(대량 복사 프로그램) API를 사용하는 명령줄 도구입니다. bcp 유틸리티는 다음 태스크를 수행합니다. <OL style="LIST-STYLE-TYPE: decimal"> <LI>데이터 파일로 SQL Server 테이블의 데이터를 대량으로 내보냅니다.</LI> <LI>쿼리의 데이터를 대량으로 내보냅니다.</LI> <LI>SQL Server 테이블로 데이터 파일의 데이터를 대량으로 가져옵니다.</LI> <LI>서식 파일을 생성합니다. </LI></OL> bcp 명령으로 bcp 유틸리티에 액세스합니다. bcp 명령을 사용하여 대량으로 데이터를 가져오려면 기존의 서식 파일이 사용되지 않는 한 테이블의 스키마와 열의 데이터 형식에 대해 알아야 합니다. bcp 유틸리티는 다른 프로그램에서 사용할 수 있도록 SQL Server 테이블의 데이터를 데이터 파일로 내보낼 수 있습니다. 또한 이 유틸리티는 일반적으로 DBMS(데이터베이스 관리 시스템)와 같은 다른 프로그램의 SQL Server 테이블로 데이터를 가져올 수 있습니다. 원본 프로그램에서 데이터 파일로 데이터를 내보낸 다음 별도의 작업으로 데이터 파일에서 SQL Server 테이블로 데이터를 복사합니다. bcp 명령은 데이터 파일의 데이터 형식과 다른 정보를 지정하는 데 사용하는 스위치를 제공합니다. 이러한 스위치가 지정되지 않은 경우 해당 명령은 데이터 파일의 데이터 필드 유형과 같은 서식 정보를 확인하는 메시지를 표시합니다. 그런 다음 명령은 대화형 응답을 포함하여 서식 파일을 만들 것인지 묻습니다. 나중에 융통성 있게 대량으로 가져오기 또는 내보내기 작업을 수행하려면 서식 파일이 유용합니다. 추후 bcp 명령으로 동등한 데이터 파일에 서식 파일을 지정할 수 있습니다. 자세한 내용은 bcp를 사용하여 데이터 형식을 호환 가능하도록 지정를 참조하십시오.  참고  MicrosoftSQL Server 버전 7.0부터 ODBC 대량 복사 API를 사용하여 bcp 유틸리티가 작성됩니다. 이전 버전의 bcp 유틸리티는 DB-Library 대량 복사 API를 사용하여 작성되었습니다.   bcp 구문에 대한 자세한 내용은 bcp 유틸리티를 참조하십시오.     BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기 이 항목에서는 Transact-SQL BULK INSERT 문 및 INSERT...SELECT * FROM OPENROWSET(BULK...) 문을 사용하여 데이터 파일에서 MicrosoftSQL Server 테이블로 데이터를 대량으로 가져오는 방법을 개괄적으로 설명합니다. BULK INSERT 및 OPENROWSET(BULK…)을 사용할 때와 이러한 방법을 사용하여 원격 데이터 원본에서 데이터를 대량으로 가져올 때의 보안 고려 사항을 설명합니다.  참고  BULK INSERT 또는 OPENROWSET(BULK…)을 사용할 경우 SQL Server 2005 이상 버전에서 가장을 처리하는 방법을 이해해야 합니다. 자세한 내용은 이 항목의 뒷부분에 나오는 "보안 고려 사항"을 참조하십시오.    BULK INSERT 문 BULK INSERT는 데이터 파일의 데이터를 테이블로 로드합니다. 이 기능은 bcp 명령의 in 옵션이 제공하는 기능과 유사하지만 SQL Server 프로세스에서 데이터 파일을 읽는다는 점이 다릅니다. BULK INSERT 구문에 대한 자세한 내용은 BULK INSERT(Transact-SQL)를 참조하십시오.     HumanResources.myTeam 테이블 만들기 여러 대량 데이터 가져오기 및 내보내기 항목에서 예로 사용되는 테이블의 작성 방법에 대해 설명합니다.     데이터를 가져오거나 내보내기 위한 데이터 형식 데이터 형식 요구 사항과 관련된 제한 및 고려 사항에 대해 설명합니다. 또한 이 항목에서는 호환성을 위한 데이터 형식의 지정 방법을 알려 줍니다.     데이터를 가져오거나 내보내기 위한 서식 파일 서식 파일에 대해 설명합니다. 서식 파일을 통해 SQL Server에서 데이터 파일을 쓰거나 읽는 융통성 있는 방법으로 성능을 향상시킵니다. 또한 bcp 유틸리티를 사용하여 서식 파일을 만드는 방법에 대해 설명합니다.     XML 문서 대량 가져오기 및 내보내기 예 XML 문서를 SQL Server 데이터베이스로 대량 가져오거나 SQL Server 데이터베이스에서 대량으로 내보낼 수 있는 방법을 보여 줍니다.     데이터 대량 가져오기 및 내보내기 시나리오 데이터를 가져오거나 내보내기 위한 일반적인 여러 시나리오에 대해 설명합니다. 대량 가져오기 성능 최적화 대량 가져오기 작업을 위해 성능을 최적화하는 방법에 대해 설명합니다.