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

아티클
03/21/2024

적용 대상: 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 문을 사용하여 데이터 이동을 수행합니다.

예시 테스트 조건

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

샘플 테이블

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

샘플 데이터 파일

빈 D:\BCP\myTestSkipField.bcp 파일을 만들고 다음 데이터를 삽입합니다.

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

서식 파일 만들기

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

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

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

비 XML 서식 파일 만들기

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

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

비 XML 서식 파일 수정

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

FirstName 에 대한 전체 서식 파일 행을 복사하고 그다음 줄에 FirstName 뒤에 직접 붙여 넣습니다.
새 행 및 모든 후속 행에 대해 호스트 파일 필드 순서 값을 하나씩 늘립니다.
데이터 파일에서 실제 필드 수 반영을 위해 열 개수 값을 늘립니다.
두 번째 서식 파일 행에서 서버 열 순서를 2 에서 0 으로 수정합니다.

변경 내용을 비교합니다.

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

이후

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

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

데이터 필드 4개
myTestSkipField.bcp의 첫 번째 데이터 필드는 첫 번째 열인 myTestSkipField.. PersonID에 매핑됩니다.
myTestSkipField.bcp의 두 번째 데이터 필드가 어떠한 열에도 매핑되지 않습니다.
myTestSkipField.bcp 의 세 번째 데이터 필드가 다음 두 번째 열에 매핑됨: myTestSkipField.. FirstName
myTestSkipField.bcp의 네 번째 데이터 필드가 세 번째 열인 myTestSkipField.. LastName에 매핑됩니다.

XML 서식 파일 만들기

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

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

XML 서식 파일 수정

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

전체 두 번째 필드를 복사하여 다음 줄의 두 번째 필드 바로 다음에 붙여넣습니다.
새 필드 및 각 후속 필드에 대해 "FIELD ID" 값을 하나씩 늘립니다.
수정된 매핑을 반영하기 위해 FirstName 및 LastName에 대해 "COLUMN 원본" 값을 1씩 늘립니다.

변경 내용을 비교합니다.

<?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="\r\n" MAX_LENGTH="30" 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"/>
</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="25" 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="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

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

데이터 필드 4개
필드 1 - 열 1에 해당, 다음 첫 번째 테이블 열에 매핑: myTestSkipField.. PersonID
필드 2 - 어떤 열에도 해당하지 않음, 따라서 어떤 테이블 열에도 매핑하지 않음
필드 3 - 열 3에 해당, 다음 두 번째 테이블 열에 매핑: myTestSkipField.. FirstName
필드 4 - 열 4에 해당, 다음 세 번째 테이블 열에 매핑: myTestSkipField.. LastName

서식 파일을 사용하여 데이터를 가져와 데이터 필드 건너뛰기

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

bcp 및 비 XML 서식 파일 사용

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

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

bcp 및 XML 서식 파일 사용

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

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

BULK INSERT 및 비 XML 서식 파일 사용

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

USE TestDatabase;  
GO

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

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

BULK INSERT 및 XML 서식 파일 사용

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

USE TestDatabase;  
GO

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

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

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

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

USE TestDatabase;
GO

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

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

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

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

USE TestDatabase;  
GO

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

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

다음을 통해 공유

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

예시 테스트 조건

샘플 테이블

샘플 데이터 파일

서식 파일 만들기

비 XML 서식 파일 만들기

비 XML 서식 파일 수정

XML 서식 파일 만들기

XML 서식 파일 수정

서식 파일을 사용하여 데이터를 가져와 데이터 필드 건너뛰기

bcp 및 비 XML 서식 파일 사용

bcp 및 XML 서식 파일 사용

BULK INSERT 및 비 XML 서식 파일 사용

BULK INSERT 및 XML 서식 파일 사용

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

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

다음 단계

피드백

추가 리소스