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

아티클
10/30/2024

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

데이터 파일에는 테이블의 해당 열과 다른 순서로 정렬된 필드가 포함될 수 있습니다. 이 문서에서는 필드가 테이블 열과 다른 순서로 정렬된 데이터 파일을 수용하도록 수정된 비 XML 및 XML 서식 파일을 모두 제공합니다. 수정된 서식 파일은 데이터 필드를 해당 테이블 열로 매핑합니다. 자세한 내용은 bcp(SQL Server)를 사용하여 서식 파일 만들기를 검토합니다.

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

참고 항목

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

예시 테스트 조건

이 문서의 수정된 형식 파일 예제는 이 문서의 뒷부분에 정의된 테이블 및 데이터 파일을 기반으로 합니다.

샘플 테이블

다음 Transact-SQL 스크립트는 테스트 데이터베이스와 이름이 지정된 myRemap테이블을 만듭니다.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

샘플 데이터 파일

다음 데이터는 표myRemap에 표시된 대로 역순으로 표시됩니다.FirstName LastName 메모장과 같은 텍스트 편집기를 사용하여 빈 파일을 D:\BCP\myRemap.bcp 만들고 다음 데이터를 삽입합니다.

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

서식 파일 만들기

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

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

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

비 XML 서식 파일 만들기

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

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

비 XML 서식 파일 수정

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

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

변경 내용 비교:

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 파일을 열고 다음을 수정합니다.

서식 파일에서 <FIELD> 요소가 선언되는 순서는 해당 필드가 데이터 파일에 표시되는 순서입니다. 따라서 ID 특성이 2와 3인 요소의 <FIELD> 순서를 반대로 바뀝니다.
ID 특성 값이 <FIELD> 순차적인지 확인합니다.
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
COLUMN 2에 해당하는 FIELD 2는 세 번째 테이블 열에 다시 매핑됩니다. myRemap.. LastName
COLUMN 3에 해당하는 FIELD 3은 두 번째 테이블 열에 다시 매핑됩니다. myRemap.. FirstName
필드 4 - 열 4에 해당, 다음 네 번째 테이블 열에 매핑: myRemap.. Gender

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

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

bcp 및 비 XML 서식 파일 사용

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

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

bcp 및 XML 서식 파일 사용

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

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

BULK INSERT 및 비 XML 서식 파일 사용

다음 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 INSERT 및 XML 서식 파일 사용

다음 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 및 비 XML 서식 파일 사용

다음 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 및 XML 서식 파일 사용

다음 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;

다음을 통해 공유

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

예시 테스트 조건

샘플 테이블

샘플 데이터 파일

서식 파일 만들기

비 XML 서식 파일 만들기

비 XML 서식 파일 수정

XML 서식 파일 만들기

XML 서식 파일 수정

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

bcp 및 비 XML 서식 파일 사용

bcp 및 XML 서식 파일 사용

BULK INSERT 및 비 XML 서식 파일 사용

BULK INSERT 및 XML 서식 파일 사용

OPENROWSET 및 비 XML 서식 파일 사용

OPENROWSET 및 XML 서식 파일 사용

피드백

추가 리소스

다음을 통해 공유

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

예시 테스트 조건

샘플 테이블

샘플 데이터 파일

서식 파일 만들기

비 XML 서식 파일 만들기

비 XML 서식 파일 수정

XML 서식 파일 만들기

XML 서식 파일 수정

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

bcp 및 비 XML 서식 파일 사용

bcp 및 XML 서식 파일 사용

BULK INSERT 및 비 XML 서식 파일 사용

BULK INSERT 및 XML 서식 파일 사용

OPENROWSET 및 비 XML 서식 파일 사용

OPENROWSET 및 XML 서식 파일 사용

관련 콘텐츠

피드백

추가 리소스