서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)

  • 아티클

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

중요 정보중요

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

[!참고]

서식 파일에 대한 자세한 내용은 비 XML 서식 파일(SQL Server) 및 XML 서식 파일(SQL Server)를 참조하십시오.

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

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

대량 로드 명령

서식 파일 옵션 사용

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 스키마의 AdventureWorks2012 예제 데이터베이스에 myTestFormatFiles라는 테이블을 만들어야 합니다. 이 테이블을 만들려면 SQL Server Management Studio 쿼리 편집기에서 다음을 실행합니다.

USE AdventureWorks2012;
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 AdventureWorks2012..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 AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

bcp 사용

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

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

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

[!참고]

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

BULK INSERT 사용

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

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

USE AdventureWorks2012;
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 AdventureWorks2012;
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

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

DROP TABLE myTestFormatFiles

[!참고]

OPENROWSET BULK 절에 대한 자세한 내용은 OPENROWSET(Transact-SQL)을 참조하십시오.

추가 예

서식 파일 만들기

서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)

서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server)

서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)

참고 항목

참조

bcp 유틸리티

BULK INSERT(Transact-SQL)

OPENROWSET(Transact-SQL)

개념

비 XML 서식 파일(SQL Server)

XML 서식 파일(SQL Server)