아티클
09/27/2010

서식 파일을 사용하여 데이터를 대량으로 가져오기

이 항목에서는 대량 가져오기 작업에서 서식 파일을 사용하는 방법에 대해 설명합니다. 서식 파일은 데이터 파일의 필드를 테이블 열에 매핑합니다. MicrosoftSQL Server 2005 이상 버전에서 bcp 명령이나 BULK INSERT 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...) Transact-SQL 명령을 사용할 때 비 XML 또는 XML 서식 파일을 사용하여 데이터를 대량으로 가져올 수 있습니다.

중요
유니코드 문자 데이터 파일에 서식 파일을 사용하려면 모든 입력 필드가 유니코드 텍스트 문자열(고정 크기 또는 문자 종료 유니코드 문자열)이어야 합니다.

[!참고]

서식 파일에 대한 자세한 내용은 비 XML 서식 파일 이해 및 XML 서식 파일 이해를 참조하십시오.

대량 가져오기 명령의 서식 파일 옵션

다음 표에서는 대량 가져오기 명령 각각에 대한 서식 파일 옵션을 요약하여 보여 줍니다.

대량 로드 명령	서식 파일 옵션 사용
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ... SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'format_file_path'
bcp … in	-fformat_file

자세한 내용은 bcp 유틸리티, BULK INSERT(Transact-SQL) 또는 OPENROWSET(Transact-SQL)을 참조하십시오.

[!참고]

SQLXML 데이터의 대량 내보내기 또는 가져오기를 수행하려면 서식 파일에서 SQLCHAR 또는 SQLVARYCHAR(데이터 정렬에 의해 포함된 코드 페이지나 클라이언트 코드 페이지로 데이터가 전송됨), SQLNCHAR 또는 SQLNVARCHAR(데이터가 유니코드로 전송됨), SQLBINARY 또는 SQLVARYBIN(데이터가 변환되지 않고 전송됨) 데이터 형식 중 하나를 사용합니다.

예

이 섹션에 나오는 예에서는 bcp 명령과 BULK INSERT 및 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문을 통해 데이터를 대량으로 가져오는 데 서식 파일을 사용하는 방법을 보여 줍니다. 대량 가져오기 예를 실행하려면 먼저 예제 테이블, 데이터 파일 및 서식 파일을 만들어야 합니다.

예제 테이블

예에서 dbo 스키마의 AdventureWorks 예제 데이터베이스에 myTestFormatFiles 테이블을 만들어야 합니다. 이 테이블을 만들려면 SQL Server Management Studio 쿼리 편집기에서 다음을 실행합니다.

USE AdventureWorks;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

예제 데이터 파일

예에서는 다음 레코드가 들어 있는 myTestFormatFiles-c.Dat 예제 데이터 파일을 사용합니다. 데이터 파일을 만들려면 Microsoft Windows 명령 프롬프트에서 다음을 입력합니다.

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

예제 서식 파일

이 섹션의 일부 예에서는 XML 서식 파일 myTestFormatFiles-f-x-c.Xml을 사용하고 다른 예에서는 비 XML 서식 파일을 사용합니다. 두 서식 파일은 모두 문자 데이터 형식과 비기본 필드 종결자(,)를 사용합니다.

예제 비 XML 서식 파일

다음 예에서는 bcp를 사용하여 myTestFormatFiles 테이블에서 XML 서식 파일을 생성합니다. myTestFormatFiles.Fmt 파일은 다음 정보를 포함합니다.

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

bcp에 format 옵션을 사용하여 이러한 서식 파일을 만들려면 Windows 명령 프롬프트에서 다음을 입력합니다.

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

서식 파일을 만드는 방법은 서식 파일 만들기를 참조하십시오.

예제 XML 서식 파일

다음 예에서는 bcp를 사용하여 myTestFormatFiles 테이블에서 XML 서식 파일을 만듭니다. myTestFormatFiles.Xml 파일은 다음 정보를 포함합니다.

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

bcp에 format 옵션을 사용하여 이러한 서식 파일을 만들려면 Windows 명령 프롬프트에서 다음을 입력합니다.

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

bcp 사용

다음 예에서는 bcp를 사용하여 myTestFormatFiles-c.Dat 데이터 파일에서 AdventureWorks 예제 데이터베이스의 HumanResources.myTestFormatFiles 테이블로 데이터를 대량으로 가져옵니다. 이 예에서는 XML 서식 파일인 MyTestFormatFiles.Xml을 사용합니다. 또한 데이터 파일을 가져오기 전에 기존 테이블 행을 모두 삭제합니다.

Windows 명령 프롬프트에서 다음을 입력합니다.

bcp AdventureWorks..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

[!참고]

이 명령에 대한 자세한 내용은 bcp 유틸리티를 참조하십시오.

BULK INSERT 사용

다음 예에서는 BULK INSERT를 사용하여 myTestFormatFiles-c.Dat 데이터 파일에서 AdventureWorks 예제 데이터베이스의 HumanResources.myTestFormatFiles 테이블로 데이터를 대량으로 가져옵니다. 이 예에서는 비 XML 서식 파일인 MyTestFormatFiles.Fmt를 사용합니다. 또한 데이터 파일을 가져오기 전에 기존 테이블 행을 모두 삭제합니다.

SQL Server Management Studio 쿼리 편집기에서 다음을 실행합니다.

USE AdventureWorks;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

[!참고]

이 문에 대한 자세한 내용은 BULK INSERT(Transact-SQL)를 참조하십시오.

OPENROWSET 대량 행 집합 공급자 사용

다음 예에서는 INSERT ... SELECT * FROM OPENROWSET(BULK...)을 사용하여 myTestFormatFiles-c.Dat 데이터 파일에서 AdventureWorks 예제 데이터베이스의 HumanResources.myTestFormatFiles 테이블로 데이터를 대량으로 가져옵니다. 이 예에서는 XML 서식 파일인 MyTestFormatFiles.Xml을 사용합니다. 또한 데이터 파일을 가져오기 전에 기존 테이블 행을 모두 삭제합니다.

SQL Server Management Studio 쿼리 편집기에서 다음을 실행합니다.

USE AdventureWorks;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

예제 테이블의 사용을 완료하면 다음 문을 사용하여 해당 테이블을 삭제할 수 있습니다.