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

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

데이터 파일에는 테이블의 열 수보다 많은 필드가 포함될 수 있습니다. 이 문서에서는 테이블 열을 해당 데이터 필드에 매핑하고 추가 필드를 무시하여 더 많은 필드가 있는 데이터 파일을 수용하도록 비 XML 및 XML 서식 파일을 모두 수정하는 방법을 설명합니다.

자세한 내용은 bcp를 사용하여 서식 파일 만들기(SQL Server)를 참조하세요.

예시 테스트 조건

이 문서의 수정된 형식 파일 예제는 샘플 테이블 myTestSkipField 및 데이터 파일을 D:\BCP\myTestSkipField.bcp기반으로 합니다. 코드 샘플의 로컬 파일 위치를 컴퓨터의 파일 위치로 변경합니다.

샘플 테이블

스크립트는 테스트 데이터베이스와 이름이 지정된 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 파일을 열고 다음을 수정합니다.

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

1. 전체 두 번째 필드를 복사하여 다음 줄의 두 번째 필드 바로 다음에 붙여넣습니다.
2. 새 FIELD ID의 FIELD 값을 1씩 늘리고, 이후의 각 FIELD에 대해서도 동일하게 값을 1씩 늘립니다.
3. 수정된 매핑을 COLUMN SOURCE 반영하기 위해 FirstNameLastName 값을 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개
• FIELD 1에 COLUMN 해당하는 1은 첫 번째 테이블 열에 매핑됩니다. myTestSkipField.. PersonID
• FIELD 2는 어떤 COLUMN 열에도 해당하지 않으므로 테이블 열에 매핑되지 않습니다.
• FIELD 3에 COLUMN 해당하는 3은 두 번째 테이블 열에 매핑됩니다. myTestSkipField.. FirstName
• FIELD 4에 COLUMN 해당하는 4는 세 번째 테이블 열에 매핑됩니다. myTestSkipField.. LastName

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

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

bcp 및 비 XML 서식 파일 사용

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

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

bcp 및 XML 형식 파일 사용(SQL Server)

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

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

BULK INSERT(Transact-SQL) 및 비 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(Transact-SQL) 및 XML 서식 파일 사용(SQL Server)

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(Transact-SQL) 및 비 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(Transact-SQL) 및 XML 형식 파일 사용(SQL Server)

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;

관련 콘텐츠

• bcp 유틸리티
• BULK INSERT(Transact-SQL)
• OPENROWSET(Transact-SQL)
• 서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)
• 서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)