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

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)

데이터 파일에는 테이블의 해당 열과 다른 순서로 정렬된 필드가 포함될 수 있습니다. 이 항목에서는 필드가 테이블 열과 다른 순서로 정렬된 데이터 파일을 포함할 수 있는 비 XML 및 XML 서식 파일을 모두 소개합니다. 수정된 서식 파일은 데이터 필드를 해당 테이블 열로 매핑합니다. 추가 정보는 서식 파일 만들기(SQL Server)를 검토하세요.

참고 항목

비 XML 서식 파일 또는 XML 서식 파일은 bcp 유틸리티 명령, BULK INSERT 문 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문을 사용하여 데이터 파일을 테이블에 대량으로 가져오는데 사용될 수 있습니다. 자세한 내용은 서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)를 참조하세요.

참고 항목

대량 삽입을 포함한 이 구문은 Azure Synapse Analytics에서 지원되지 않습니다. Azure Synapse Analytics 및 기타 클라우드 데이터베이스 플랫폼 통합에서 Azure Data Factory의 COPY 문을 통해 또는 COPY INTO 및 PolyBase와 같은 T-SQL 문을 사용하여 데이터 이동을 수행합니다.

예제 테스트 조건

이 항목의 수정된 서식 파일 예제는 아래에 정의된 테이블 및 데이터 파일을 기반으로 합니다.

샘플 테이블

아래 스크립트는 테스트 데이터베이스와 이름이 myRemap인 테이블을 만듭니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

샘플 데이터 파일

아래 데이터는 테이블 myRemap에 표시된 역순으로 FirstNameLastName을 표시합니다. Windows 메모장을 사용하여 빈 파일 D:\BCP\myRemap.bcp를 만들고 다음 데이터를 삽입합니다.

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

서식 파일 만들기

myRemap.bcp 의 데이터를 myRemap 테이블로 대량으로 가져오려면 서식 파일에서 다음과 같은 작업을 수행해야 합니다.

  • 첫 번째 데이터 필드를 첫 번째 열인 PersonID에 매핑합니다.
  • 두 번째 데이터 필드를 세 번째 열인 LastName에 매핑합니다.
  • 세 번째 데이터 필드를 두 번째 열인 FirstName에 매핑합니다.
  • 네 번째 데이터 필드를 네 번째 열인 Gender에 매핑합니다.

서식 파일을 만드는 가장 간단한 방법은 bcp 유틸리티를 사용하는 것입니다. 먼저 기존 테이블에서 기본 서식 파일을 만듭니다. 둘째, 실제 데이터 파일을 반영하도록 기본 서식 파일을 수정합니다.

비 XML 서식 파일 만들기

자세한 내용은 비 XML 서식 파일(SQL Server)을 검토하세요. 다음 명령은 bcp 유틸리티를 사용하여 myRemap.fmt 스키마에 따라 비 XML 서식 파일 myRemap을 생성합니다. 또한 한정자 c는 문자 데이터를 지정하는 데 사용하고, t,는 쉼표를 필드 종결자로 지정하는 데 사용하며 T는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용합니다. 명령 프롬프트에서 다음 명령을 입력합니다.

bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

비 XML 서식 파일 수정

용어는 비 XML 서식 파일의 구조를 참조하세요. Windows 메모장에서 D:\BCP\myRemap.fmt 파일을 열고 다음을 수정합니다.

  1. 행이 myRemap.bcp의 데이터와 순서가 같도록 서식 파일 행의 순서를 다시 정렬합니다.
  2. 호스트 파일 필드 순서 값이 순차적인지 확인합니다.
  3. 마지막 서식 파일 행 다음에 캐리지 리턴이 있는지 확인합니다.

변경 내용 비교:

이전

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

이후

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

수정된 서식 파일은 이제 다음을 반영합니다.

  • myRemap.bcp의 첫 번째 데이터 필드는 첫 번째 열인 myRemap.. PersonID에 매핑됩니다.
  • myRemap.bcp의 두 번째 데이터 필드가 세 번째 열인 myRemap.. LastName 매핑됩니다.
  • myRemap.bcp 의 세 번째 데이터 필드가 다음 두 번째 열에 매핑됨: myRemap.. FirstName
  • myRemap.bcp의 네 번째 데이터 필드가 네 번째 열인 myRemap.. Gender에 매핑됩니다.

XML 서식 파일 만들기

자세한 내용은 XML 서식 파일(SQL Server)을 검토하세요. 다음 명령은 bcp 유틸리티를 사용하여 myRemap.xml 스키마에 따라 myRemap XML 서식 파일을 생성합니다 또한 한정자 c는 문자 데이터를 지정하는 데 사용하고, t,는 쉼표를 필드 종결자로 지정하는 데 사용하며 T는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용합니다. x 한정자는 XML 기반 서식 파일을 생성하는 데 사용해야 합니다. 명령 프롬프트에서 다음 명령을 입력합니다.

bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

XML 서식 파일 수정

용어는 XML 서식 파일의 스키마 구문을 참조하세요. Windows 메모장에서 D:\BCP\myRemap.xml 파일을 열고 다음을 수정합니다.

  1. 서식 파일에서 <FIELD> 요소가 선언되는 순서는 이들 필드가 데이터 파일에 나열되는 순서로, ID 특성이 2이고 3인 <FIELD> 요소 순서와 반대가 됩니다.
  2. <FIELD> ID 특성 값이 순차적인지 확인합니다.
  3. <ROW> 요소에서 <COLUMN> 요소의 순서에 따라 대량 작업에서 대상으로 전송하는 순서가 정의됩니다. XML 서식 파일은 각 <COLUMN> 요소에 대량 가져오기 작업의 대상 테이블에 있는 열과 관계가 없는 로컬 이름을 지정합니다. <COLUMN> 요소의 순서는 <RECORD> 정의에 있는 <FIELD> 요소의 순서와는 독립적입니다. 각 <COLUMN> 요소는 ID가 <COLUMN> 요소의 SOURCE 특성에서 지정되는 <FIELD> 요소에 해당합니다. 따라서 <COLUMN> SOURCE에 대한 값은 버전이 필요한 특성뿐입니다. <COLUMN> SOURCE 특성 2와 3에 대한 순서를 반대로 설정합니다.

변경 내용 비교:

이전

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

이후

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

수정된 서식 파일은 이제 다음을 반영합니다.

  • 필드 1 - 열 1에 해당, 다음 첫 번째 테이블 열에 매핑: myRemap.. PersonID
  • 필드 2 - 열 2에 해당, 다음 세 번째 테이블 열에 다시 매핑: myRemap.. LastName
  • 필드 3 - 열 3에 해당, 다음 두 번째 테이블 열에 다시 매핑: myRemap.. FirstName
  • 필드 4 - 열 4에 해당, 다음 네 번째 테이블 열에 매핑: myRemap.. Gender

서식 파일이 있는 데이터를 가져와 테이블 열을 데이터 파일 필드에 매핑

아래 예제에서는 위에서 만든 데이터베이스, 데이터 파일 및 서식 파일을 사용합니다.

bcp비 XML 서식 파일 사용

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

bcpXML 서식 파일 사용

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

BULK INSERT비 XML 서식 파일 사용

Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap  
   FROM 'D:\BCP\myRemap.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

BULK INSERTXML 서식 파일 사용

Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap  
   FROM 'D:\BCP\myRemap.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myRemap.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

OPENROWSET(BULK...)비 XML 서식 파일 사용

Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

OPENROWSET(BULK...)XML 서식 파일 사용

Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

다음 단계