서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)
적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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
에 표시된 역순으로 FirstName
및 LastName
을 표시합니다. 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
파일을 열고 다음을 수정합니다.
- 행이
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> 요소 순서와 반대가 됩니다.
- <FIELD> ID 특성 값이 순차적인지 확인합니다.
- <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
bcp 및 XML 서식 파일 사용
명령 프롬프트에서 다음 명령을 입력합니다.
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 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.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;