서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)

아티클
03/22/2024

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

이 문서에서는 원본 데이터 파일에 건너뛴 열의 데이터가 없는 경우 서식 파일을 사용하여 테이블 열 가져오기를 건너뛰는 방법을 설명합니다. 데이터 파일에는 대상 테이블의 열 수보다 적은 필드가 포함될 수 있습니다. 즉, 대상 테이블에서 다음 두 조건 중 하나 이상이 true인 경우에만 열 가져오기를 건너뛸 수 있습니다.

건너뛴 열이 Null을 허용합니다.
건너뛴 열에 기본값이 있습니다.

참고 항목

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

샘플 테이블 및 데이터 파일

이 문서의 예제에서는 dbo 스키마 아래에 myTestSkipCol 이름이 지정된 테이블이 있습니다. WideWorldImporters 또는 AdventureWorks와 같은 샘플 데이터베이스 또는 다른 데이터베이스에서 이 테이블을 만들 수 있습니다. 다음과 같이 테이블을 만듭니다.

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol  
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO

이 문서의 예제에서는 샘플 데이터 파일 myTestSkipCol2.dat도 사용합니다. 대상 테이블에는 열이 세 개 포함되어 있지만 이 데이터 파일에는 두 개의 필드만 포함됩니다.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3

기본 단계

비 XML 서식 파일 또는 XML 서식 파일을 사용하여 테이블 열을 건너뛸 수 있습니다. 두 경우 모두 다음 두 단계를 수행하세요.

bcp 명령줄 유틸리티를 사용하여 기본 서식 파일을 만듭니다.
텍스트 편집기에서 기본 서식 파일을 수정합니다.

수정된 서식 파일은 각 기존 필드를 대상 테이블의 해당 열에 매핑해야 합니다. 또한 건너뛸 테이블 열 또는 열을 나타내야 합니다.

예를 들어 데이터를 myTestSkipCol2.dat에서 myTestSkipCol 테이블로 대량으로 가져오려면 서식 파일은 첫 번째 데이터 필드를 Col1에 매핑하고, Col2를 건너뛰고, 두 번째 필드를 Col3에 매핑해야 합니다.

옵션 #1 - 비 XML 서식 파일 사용

1단계 - 기본 비 XML 서식 파일 만들기

명령 프롬프트에서 다음 bcp 명령을 실행하여 myTestSkipCol 샘플 테이블에 대한 기본 비 XML 서식 파일을 만듭니다.

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Important

-S 인수를 사용하여 연결할 서버 인스턴스의 이름을 지정해야 할 수 있습니다. 또한 -U 및 -P 인수를 사용하여 사용자 이름과 암호를 지정해야 할 수도 있습니다. 자세한 내용은 bcp Utility를 참조하세요.

위 명령은 myTestSkipCol_Default.fmt라는 비 XML 서식 파일을 만듭니다. 이 서식 파일은 bcp에서 생성된 양식이므로 기본 서식 파일이라고 합니다. 기본 서식 파일은 데이터 파일 필드와 테이블 열 간의 일 대 일 대응을 나타냅니다.

다음 스크린샷에서는 이 샘플 기본 서식 파일의 값을 보여줍니다.

mytestskipcol에 대한 비 XML 형식 기본 파일을 자세히 보여주는 다이어그램.

참고 항목

서식 파일 필드에 대한 자세한 내용은 비 XML 서식 파일(SQL Server)을 참조하세요.

2단계 - 비 XML 서식 파일 수정

기본 비 XML 서식 파일을 수정하려면 두 가지 대안이 있습니다. 대안 중 하나는 데이터 필드가 데이터 파일에 없고 해당 테이블 열에 삽입할 데이터가 없음을 나타냅니다.

테이블 열을 건너뛰려면 다음 대체 방법 중 하나를 사용하여 기본 비 XML 서식 파일을 편집하고 파일을 수정합니다.

옵션 #1 - 행 제거

열을 건너뛰는 기본 방법에는 다음 세 단계가 포함됩니다.

먼저 원본 데이터 파일에서 누락된 필드를 나타내는 모든 서식 파일 행을 삭제합니다.
그런 다음 삭제된 행 뒤에 오는 각 서식 파일 행의 "호스트 파일 필드 순서" 값을 줄입니다. 목표는 데이터 파일에서 각 데이터 필드의 실제 위치를 반영하는 순차적 "호스트 파일 필드 순서" 값(1~n)입니다.
마지막으로 데이터 파일의 실제 필드 수를 반영하여 "열의 수" 필드의 값을 줄입니다.

다음 예제는 myTestSkipCol 테이블의 기본 서식 파일을 기반으로 합니다. 이 수정된 서식 파일은 첫 번째 데이터 필드를 Col1에 매핑하고, Col2를 건너뛰고, 두 번째 데이터 필드를 Col3에 매핑합니다. Col2 행이 삭제되었습니다. 첫 번째 필드 뒤에 분리된 구분 기호도 \t에서 ,로 변경되었습니다.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

옵션 #2 - 행 정의 수정

또는 테이블 열을 건너뛰려면 테이블 열에 해당하는 서식 파일 행의 정의를 수정할 수 있습니다. 이 서식 파일 행에서는 "접두사 길이", "호스트 파일 데이터 길이", "서버 열 순서" 값을 0으로 설정해야 합니다. 또한 "종결자" 및 "열 데이터 정렬" 필드를 ""(빈 값 또는 NULL 값)로 설정해야 합니다. 실제 열 이름이 필요하지 않더라도 “서버 열 이름” 값에는 공백이 아닌 문자열이 필요합니다. 나머지 형식 필드는 기본값으로 둡니다.

다음 예제는 myTestSkipCol 테이블의 기본 서식 파일에서도 파생됩니다.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

비 XML 서식 파일의 예

다음 예는 이 문서의 앞부분에서 설명한 myTestSkipCol 샘플 테이블 및 myTestSkipCol2.dat 샘플 데이터 파일을 기반으로 합니다.

BULK INSERT 사용

이 예제는 이전 섹션에서 설명한 대로 만든 수정된 비 XML 서식 파일 중 하나를 사용하여 작동합니다. 이 예제에서는 수정된 서식 파일의 이름을 myTestSkipCol2.fmt로 합니다. BULK INSERT를 사용하여 myTestSkipCol2.dat 데이터 파일을 대량으로 가져오려면 SSMS(SQL Server Management Studio)에서 다음 코드를 실행합니다. 컴퓨터에서 샘플 파일의 위치에 대한 파일 시스템 경로를 업데이트합니다.

USE WideWorldImporters;  
GO  
BULK INSERT myTestSkipCol  
   FROM 'C:\myTestSkipCol2.dat'  
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO

옵션 #2 - XML 서식 파일 사용

1단계 - 기본 XML 서식 파일 만들기

명령 프롬프트에서 다음 bcp 명령을 실행하여 myTestSkipCol 샘플 테이블에 대한 기본 XML 서식 파일을 만듭니다.

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Important

위 명령은 myTestSkipCol_Default.xml이라는 XML 서식 파일을 만듭니다. 이 서식 파일은 bcp에서 생성된 양식이므로 기본 서식 파일이라고 합니다. 기본 서식 파일은 데이터 파일 필드와 테이블 열 간의 일 대 일 대응을 나타냅니다.

<?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="\t" MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

참고 항목

XML 서식 파일의 구조에 대한 자세한 내용은 XML 서식 파일(SQL Server)을 참조하세요.

2단계 - XML 서식 파일 수정

다음은 Col2를 건너뛰는 수정된 XML 서식 파일 myTestSkipCol2.xml입니다. Col2의 FIELD 및 ROW 항목이 제거되고 항목의 번호가 다시 매겨졌습니다. 첫 번째 필드 뒤에 분리된 구분 기호도 \t에서 ,로 변경되었습니다.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

XML 서식 파일의 예

다음 예는 이 문서의 앞부분에서 설명한 myTestSkipCol 샘플 테이블 및 myTestSkipCol2.dat 샘플 데이터 파일을 기반으로 합니다.

myTestSkipCol2.dat의 데이터를 myTestSkipCol 테이블로 가져오기 위해 이 예에서는 수정된 XML 서식 파일인 myTestSkipCol2.xml을 사용합니다.

보기와 함께 BULK INSERT 사용

XML 서식 파일을 사용하면 bcp 명령이나 BULK INSERT 문을 사용하여 테이블로 데이터를 직접 가져올 때 열을 건너뛸 수 없습니다. 그러나 테이블의 마지막 열을 제외한 모든 열로 가져올 수 있습니다. 마지막 열이 아닌 열을 건너뛰어야 하는 경우 데이터 파일에 포함된 열만 포함하는 대상 테이블의 뷰를 만들어야 합니다. 그런 다음 해당 파일에서 뷰로 데이터를 대량으로 가져올 수 있습니다.

다음 예에서는 myTestSkipCol 테이블에 v_myTestSkipCol 뷰를 만듭니다. 이 뷰는 두 번째 테이블 열(Col2)을 건너뜁니다. 이 예제에서는 BULK INSERT를 사용하여 myTestSkipCol2.dat 데이터 파일을 이 뷰로 가져옵니다.

SSMS에서 다음 코드를 실행합니다. 컴퓨터에서 샘플 파일의 위치에 대한 파일 시스템 경로를 업데이트합니다.

USE WideWorldImporters;  
GO

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO

BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO

OPENROWSET(BULK...) 사용

OPENROWSET(BULK...)를 사용하여 XML 서식 파일로 테이블 열을 건너뛰려면 다음과 같이 선택 목록과 대상 테이블에 명시적인 열 목록을 제공해야 합니다.

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

다음 예제에서는OPENROWSET 대량 행 집합 공급자 및 myTestSkipCol2.xml 서식 파일을 사용합니다. 이 예제에서는 myTestSkipCol2.dat 데이터 파일을 myTestSkipCol 테이블로 대량으로 가져옵니다. 문에는 필요에 따라 선택 목록뿐 아니라 대상 테이블에도 있는 열의 명시적 목록이 포함됩니다.

SSMS에서 다음 코드를 실행합니다. 컴퓨터에서 샘플 파일의 위치에 대한 파일 시스템 경로를 업데이트합니다.

USE WideWorldImporters;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;  
GO

다음을 통해 공유

서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)

샘플 테이블 및 데이터 파일

기본 단계

옵션 #1 - 비 XML 서식 파일 사용

1단계 - 기본 비 XML 서식 파일 만들기

2단계 - 비 XML 서식 파일 수정

옵션 #1 - 행 제거

옵션 #2 - 행 정의 수정

비 XML 서식 파일의 예

BULK INSERT 사용

옵션 #2 - XML 서식 파일 사용

1단계 - 기본 XML 서식 파일 만들기

2단계 - XML 서식 파일 수정

XML 서식 파일의 예

보기와 함께 BULK INSERT 사용

OPENROWSET(BULK...) 사용

다음 단계

피드백

추가 리소스