서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)Use a Format File to Skip a Table Column (SQL Server)

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스예Azure SQL 데이터 웨어하우스 예 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

이 문서에서는 원본 데이터 파일에 건너뛴 열의 데이터가 없는 경우 서식 파일을 사용하여 테이블 열 가져오기를 건너뛰는 방법을 설명합니다.This article describes how to use a format file to skip importing a table column when the data for the skipped column does not exist in the source data file. 데이터 파일에는 대상 테이블의 열 수보다 적은 수의 필드가 포함될 수 있습니다. 즉, 대상 테이블에서 다음 두 조건 중 하나 이상이 사실인 경우에만 열 가져오기를 건너뛸 수 있습니다.A data file can contain fewer fields than the number of columns in the destination table - that is, you can skip importing a column - only if at least one of the following two conditions is true in the destination table:

  • 건너뛴 열이 Null을 허용합니다.The skipped column is nullable.
  • 건넌뛴 열에 기본값이 있습니다.The skipped column has a default value.

예제 테이블 및 데이터 파일Sample table and data file

이 아티클의 예에서는 dbo 스키마에서 myTestSkipCol이라는 테이블을 기대합니다.The examples in this article expect a table named myTestSkipCol under the dbo schema. WideWorldImporters 또는 AdventureWorks와 같은 샘플 데이터베이스 또는 다른 데이터베이스에서 이 테이블을 만들 수 있습니다.You can create this table in a sample database such as WideWorldImporters or AdventureWorks or in any other database. 이 테이블을 다음과 같이 만듭니다.Create this table as follows:

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

이 아티클의 예에서는 샘플 데이터 파일 myTestSkipCol2.dat도 사용합니다.The examples in this article also use a sample data file, myTestSkipCol2.dat. 대상 테이블에는 세 개의 열이 있지만 이 데이터 파일에는 두개의 필드만 포함됩니다.This data file contains only two fields, although the destination table contains three columns.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3  

기본 단계Basic steps

비 XML 서식 파일 또는 XML 서식 파일을 사용하여 테이블 열을 건너뛸 수 있습니다.You can use a non-XML format file or an XML format file to skip a table column. 두 경우 모두 다음 두 단계를 수행하세요.In both cases, there are two steps:

  1. bcp 명령줄 유틸리티를 사용하여 기본 서식 파일을 만듭니다.Use the bcp command-line utility to create a default format file.

  2. 텍스트 편집기에서 기본 서식 파일을 수정합니다.Modify the default format file in a text editor.

수정된 서식 파일은 각 기존 필드를 대상 테이블의 해당 열로 매핑해야 합니다.The modified format file must map each existing field to its corresponding column in the destination table. 또한 테이블 열 또는 건너뛸 열을 표시해야 합니다.It must also indicate which table column or columns to skip.

예를 들어 myTestSkipCol2.dat에서 myTestSkipCol 테이블로 데이터를 대량으로 가져오려면 서식 파일은 첫 번째 데이터 필드를 Col1에 매핑하고 Col2를 건너뛴 다음, 두 번째 필드를 Col3에 매핑해야 합니다.For example, to bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, skip Col2, and map the second field to Col3.

옵션 #1 - 비 XML 서식 파일 사용Option #1 - Use a non-XML format file

단계 #1 - 기본 비 XML 서식 파일 만들기Step #1 - Create a default non-XML format file

명령 프롬프트에서 다음 bcp 명령을 실행하여 myTestSkipCol 샘플 테이블에 대한 기본 비 XML 서식 파일을 만듭니다.Create a default non-XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

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

중요

-S 인수로 연결할 서버 인스턴스의 이름을 지정해야 합니다.You might have to specify the name of the server instance to which you are connecting with the -S argument. 또한 -U-P 인수를 사용하여 사용자 이름과 암호를 지정해야 할 수도 있습니다.Also, you might have to specify the user name and password with the -U and -P arguments. 자세한 내용은 bcp Utility를 참조하세요.For more information, see bcp Utility.

위 명령은 myTestSkipCol_Default.fmt라는 비 XML 서식 파일을 만듭니다.The previous command creates a non-XML format file, myTestSkipCol_Default.fmt. 이 서식 파일은 bcp 에서 생성되는 형식이므로 기본 서식 파일이라고 합니다.This format file is called a default format file because it is the form generated by bcp. 기본 서식 파일은 데이터 파일 필드와 테이블 열 간의 일 대 일 대응을 나타냅니다.A default format file describes a one-to-one correspondence between data-file fields and table columns.

다음 스크린샷에서는 이 샘플 기본 서식 파일의 값을 보여줍니다.The following screenshot shows the values in this sample default format files.

myTestSkipCol의 기본 비 XML 형식 파일default non-XML format file for myTestSkipCol

참고

서식 파일 필드에 대한 자세한 내용은 비 XML 서식 파일(SQL Server)을 참조하세요.For more information about the format-file fields, see Non-XML Format Files (SQL Server).

단계 #2 - 비 XML 서식 파일 수정Step #2 - Modify a non-XML format file

기본 비 XML 서식 파일을 수정하려면 두 가지 방법이 있습니다.To modify a default non-XML format file, there are two alternatives. 각 방법은 데이터 필드가 데이터 파일에 없고 데이터가 해당 테이블 열에 삽입되지 않음을 나타냅니다.Either alternative indicates that the data field does not exist in the data file and that no data is to be inserted into the corresponding table column.

테이블 열을 건너뛰려면 기본 비 XML 서식 파일을 편집하고 다음 방법 중 하나를 사용하여 해당 파일을 수정합니다.To skip a table column, edit the default non-XML format file and modify the file by using one of the following alternative methods:

옵션 #1 - 행 제거Option #1 - Remove the row

열을 건너뛰는 데 기본적으로 사용되는 방법에는 다음 세 가지 단계가 포함됩니다.The preferred method for skipping a column involves the following three steps:

  1. 먼저 원본 데이터 파일에서 누락된 필드를 나타내는 모든 서식 파일 행을 삭제합니다.First, delete any format-file row that describes a field that is missing from the source data file.
  2. 그런 다음 삭제된 행 뒤에 오는 각 서식 파일 행의 "호스트 파일 필드 순서" 값을 줄입니다.Then, reduce the "Host file field order" value of each format-file row that follows a deleted row. 목표는 데이터 파일에서 각 데이터 필드의 실제 위치를 반영하는 1부터 n까지의 순차적인 "호스트 파일 필드 순서" 값을 얻는 것입니다.The goal is sequential "Host file field order" values, 1 through n, that reflect the actual position of each data field in the data file.
  3. 마지막으로 데이터 파일의 실제 필드 수를 반영하도록 "열 수" 필드의 값을 줄입니다.Finally, reduce the value in the "Number of columns" field to reflect the actual number of fields in the data file.

다음 예는 myTestSkipCol 테이블에 대한 기본 서식 파일을 기반으로 합니다.The following example is based on the default format file for the myTestSkipCol table. 수정된 이 서식 파일은 첫 번째 데이터 필드를 Col1에 매핑하고 Col2를 건너뛴 다음 두 번째 데이터 필드를 Col3에 매핑합니다.This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. Col2 의 행은 삭제되었습니다.The row for Col2 has been deleted. 첫 번째 필드 뒤의 구분 기호도 \t에서 ,로 변경되었습니다.The delimiter after the first field has also been changed from \t to ,.

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

옵션 #2 - 행 정의 수정Option #2 - Modify the row definition

테이블 열을 건너뛰기 위해 테이블 열에 해당하는 서식 파일 행의 정의를 수정할 수도 있습니다.Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. 이 서식 파일 행에서 "접두사 길이", "호스트 파일 데이터 길이" 및 "서버 열 순서" 값은 0으로 설정해야 하며In this format-file row, the "prefix length," "host file data length," and "server column order" values must be set to 0. “종결자” 및 “열 데이터 정렬” 필드도 ""(즉, 빈 값이나 NULL 값)로 설정해야 합니다.Also, the "terminator" and "column collation" fields must be set to "" (that is, to an empty or NULL value). 실제 열 이름이 필요하지 않더라도 “서버 열 이름” 값에는 공백이 아닌 문자열이 필요합니다.The "server column name" value requires a non-blank string, though the actual column name is not necessary. 나머지 서식 필드에는 해당 기본값이 필요합니다.The remaining format fields require their default values.

다음 예도 myTestSkipCol 테이블에 대한 기본 서식 파일에서 파생된 것입니다.The following example is also derived from the default format file for the myTestSkipCol table.

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 서식 파일의 예Examples with a non-XML format file

다음 예는 이 문서의 앞부분에 나오는 myTestSkipCol 샘플 테이블과 myTestSkipCol2.dat 샘플 데이터 파일을 기반으로 합니다.The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

BULK INSERT 사용Using BULK INSERT

이 예는 이전 섹션에서 설명한 대로 생성되어진 수정된 비 XML 서식 파일 중 하나를 사용하여 작동 합니다.This example works by using either of the modified non-XML format files created as described in the preceding section. 이 예에서 수정된 서식 파일의 이름은 myTestSkipCol2.fmt입니다.In this example, the modified format file is named myTestSkipCol2.fmt. BULK INSERT를 사용하여 myTestSkipCol2.dat 데이터 파일을 대량으로 가져오려면 SSMS에서 다음 코드를 실행합니다.To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in SSMS, run the following code. 컴퓨터에서 샘플 파일 위치에 대한 파일 시스템 경로를 업데이트합니다.Update the file system paths for the location of the sample files on your computer.

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

옵션 #2 - XML 서식 파일 사용Option #2 - Use an XML format file

단계 #1 - 기본 XML 서식 파일 만들기Step #1 - Create a default XML format file

명령 프롬프트에서 다음 bcp 명령을 실행하여 myTestSkipCol 샘플 테이블에 대한 기본 XML 서식 파일을 만듭니다.Create a default XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

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

중요

-S 인수로 연결할 서버 인스턴스의 이름을 지정해야 합니다.You might have to specify the name of the server instance to which you are connecting with the -S argument. 또한 -U-P 인수를 사용하여 사용자 이름과 암호를 지정해야 할 수도 있습니다.Also, you might have to specify the user name and password with the -U and -P arguments. 자세한 내용은 bcp Utility를 참조하세요.For more information, see bcp Utility.

위 명령은 myTestSkipCol_Default.xml이라는 XML 서식 파일을 만듭니다.The previous command creates an XML format file, myTestSkipCol_Default.xml. 이 서식 파일은 bcp 에서 생성되는 형식이므로 기본 서식 파일이라고 합니다.This format file is called a default format file because it is the form generated by bcp. 기본 서식 파일은 데이터 파일 필드와 테이블 열 간의 일 대 일 대응을 나타냅니다.A default format file describes a one-to-one correspondence between data-file fields and table columns.

<?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)을 참조하세요.For information about the structure of XML format files, see XML Format Files (SQL Server).

단계 #2 - XML 서식 파일 수정Step #2 - Modify an XML format file

여기서 myTestSkipCol2.xmlCol2를 건너뛴 수정된 XML 서식 파일입니다.Here is the modified XML format file, myTestSkipCol2.xml, which skips Col2. Col2에 대한 FIELDROW 항목이 제거되고 항목 번호가 다시 지정되었습니다.The FIELD and ROW entries for Col2 have been removed and the entries have been renumbered. 첫 번째 필드 뒤의 구분 기호도 \t에서 ,로 변경되었습니다.The delimiter after the first field has also been changed from \t to ,.

<?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 서식 파일의 예Examples with an XML format file

다음 예는 이 문서의 앞부분에 나오는 myTestSkipCol 샘플 테이블과 myTestSkipCol2.dat 샘플 데이터 파일을 기반으로 합니다.The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

myTestSkipCol2.dat의 데이터를 myTestSkipCol 테이블로 가져오기 위해 이 예에서는 수정된 XML 서식 파일인 myTestSkipCol2.xml을 사용합니다.To import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the modified XML format file, myTestSkipCol2.xml.

뷰와 함께 BULK INSERT 사용Using BULK INSERT with a view

XML 서식 파일을 사용하면 bcp 명령이나 BULK INSERT 문을 사용하여 테이블로 데이터를 직접 가져올 때 열을 건너뛸 수 없습니다.With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. 그러나 테이블의 마지막 열을 제외하고 모든 열로 가져올 수 있습니다.However, you can import into all but the last column of a table. 마지막 열을 제외하고 모든 열을 건너뛰어야 하는 경우 데이터 파일에 있는 열만 포함된 대상 테이블의 뷰를 만들어야 합니다.If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file. 그런 다음 해당 파일의 데이터를 뷰로 대량 가져오기를 수행할 수 있습니다.Then, you can bulk import data from that file into the view.

다음 예에서는 myTestSkipCol 테이블에 v_myTestSkipCol 뷰를 만듭니다.The following example creates the v_myTestSkipCol view on the myTestSkipCol table. 이 뷰는 두 번째 테이블 열인 Col2를 건너뜁니다.This view skips the second table column, Col2. 그런 다음 이 예에서는 BULK INSERT 를 사용하여 myTestSkipCol2.dat 데이터 파일을 이 뷰로 가져옵니다.The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

SSMS에서 다음 코드를 실행합니다.In SSMS, run the following code. 컴퓨터에서 샘플 파일 위치에 대한 파일 시스템 경로를 업데이트합니다.Update the file system paths for the location of the sample files on your computer.

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...) 사용Using OPENROWSET(BULK...)

OPENROWSET(BULK...)를 사용하여 XML 서식 파일로 테이블 열을 건너뛰려면 다음과 같이 선택 목록과 대상 테이블에 명시적인 열 목록을 제공해야 합니다.To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide an explicit list of columns in the select list and also in the target table, as follows:

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

다음 예에서는 OPENROWSET 대량 행 집합 공급자와 myTestSkipCol2.xml 서식 파일을 사용합니다.The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. 또한 myTestSkipCol2.dat 데이터 파일을 myTestSkipCol 테이블로 대량 가져옵니다.The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. 이 문에는 필요에 따라 SELECT 목록과 대상 테이블의 명시적인 열 목록이 있습니다.The statement contains an explicit list of columns in the select list and also in the target table, as required.

SSMS에서 다음 코드를 실행합니다.In SSMS, run the following code. 컴퓨터에서 샘플 파일 위치에 대한 파일 시스템 경로를 업데이트합니다.Update the file system paths for the location of the sample files on your computer.

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

참고 항목See Also

bcp Utility bcp Utility
BULK INSERT(Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET(Transact-SQL) OPENROWSET (Transact-SQL)
서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server) Use a Format File to Skip a Data Field (SQL Server)
서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)Use a Format File to Bulk Import Data (SQL Server)