이 항목에서는 서식 파일에 대해 설명합니다. 데이터 파일에 필드가 없는 경우 서식 파일을 사용하여 테이블 열 가져오기를 건너뛸 수 있습니다. 건너뛴 열이 null 허용 및/또는 기본값이 있는 경우에만 데이터 파일에 테이블의 열 수보다 적은 필드가 포함될 수 있습니다.
샘플 테이블 및 데이터 파일
다음 예제에서는 dbo 스키마 아래 AdventureWorks2012 샘플 데이터베이스에 이름이 지정된 myTestSkipCol 테이블이 필요합니다. 다음과 같이 테이블을 만듭니다.
USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipCol
(
Col1 smallint,
Col2 nvarchar(50) NULL,
Col3 nvarchar(50) not NULL
);
GO
다음 예제에서는 해당 테이블에 세 개의 열이 포함되어 있지만 두 개의 필드만 포함하는 샘플 데이터 파일을 myTestSkipCol2.dat사용합니다.
1,DataForColumn3
1,DataForColumn3
1,DataForColumn3
데이터를 myTestSkipCol2.dat에서 myTestSkipCol 테이블로 대량 가져오려면, 서식 파일은 첫 번째 데이터 필드를 Col1에 매핑하고, 두 번째 필드를 Col3에 매핑하며, Col2를 건너뛰어야 합니다.
비 XML 서식 파일 사용
XML이 아닌 서식 파일을 수정하여 테이블 열을 건너뛸 수 있습니다. 일반적으로 bcp 유틸리티를 사용하여 기본 비 XML 서식 파일을 만들고 텍스트 편집기에서 기본 파일을 수정하는 작업이 포함됩니다. 수정된 서식 파일은 각 기존 필드를 해당 테이블 열에 매핑하고 건너뛸 테이블 열 또는 열을 지정해야 합니다. 기본 비 XML 데이터 파일을 수정하기 위한 두 가지 대안이 있습니다. 둘 중 하나의 대안은 데이터 필드가 데이터 파일에 없고 해당 테이블 열에 데이터가 삽입되지 않음을 나타냅니다.
기본 비 XML 서식 파일 만들기
이 항목에서는 myTestSkipCol 샘플 테이블을 위해 생성된 기본 비 XML 형식 파일을 다음 bcp 명령을 사용하여 이용합니다.
bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T
위 명령은 myTestSkipCol_Default.fmt라는 비 XML 서식 파일을 만듭니다. 이 서식 파일은 bcp에서 생성된 양식이므로 기본 서식 파일이라고 합니다. 일반적으로 기본 형식 파일은 데이터 파일 필드와 테이블 열 간의 일대일 대응을 설명합니다.
중요합니다
연결할 서버 인스턴스의 이름을 지정해야 할 수도 있습니다. 또한 사용자 이름과 암호를 지정해야 할 수도 있습니다. 자세한 내용은 bcp Utility를 참조하세요.
다음 그림에서는 이 샘플 기본 형식 파일의 값을 보여 줍니다. 또한 각 서식 파일 필드의 이름도 보여 줍니다.
비고
서식 파일 필드에 대한 자세한 내용은 비 XML 서식 파일(SQL Server)을 참조하세요.
비 XML 서식 파일을 수정하는 메서드
테이블 열을 건너뛰려면 다음 대체 방법 중 하나를 사용하여 기본 비 XML 서식 파일을 편집하고 파일을 수정합니다.
기본 설정 방법에는 세 가지 기본 단계가 포함됩니다. 먼저 데이터 파일에서 누락된 필드를 설명하는 서식 파일 행을 삭제합니다. 그런 다음 삭제된 행 뒤에 오는 각 서식 파일 행의 "호스트 파일 필드 순서" 값을 줄입니다. 목표는 데이터 파일에서 각 데이터 필드의 실제 위치를 반영하는 순차적 "호스트 파일 필드 순서" 값(1~n)입니다. 마지막으로 데이터 파일의 실제 필드 수를 반영하여 "열의 수" 필드의 값을 줄입니다.
다음 예제는 이 항목의 앞부분에 있는 "기본 비 XML 서식 파일
myTestSkipCol만들기"에서 만든 테이블의 기본 서식 파일을 기반으로 합니다. 이 수정된 서식 파일은 첫 번째 데이터 필드를Col1에 매핑하고,Col2를 건너뛰고, 두 번째 데이터 필드를Col3에 매핑합니다.Col2행이 삭제되었습니다. 다른 수정 사항은 굵게 표시됩니다.9.0 2 1 SQLCHAR 0 7 "\t" 1 Col1 "" 2 SQLCHAR 0 100 "\r\n" 3 Col3 SQL_Latin1_General_CP1_CI_AS또는 테이블 열을 건너뛰려면 테이블 열에 해당하는 서식 파일 행의 정의를 수정할 수 있습니다. 이 서식 파일 행에서는 "접두사 길이", "호스트 파일 데이터 길이", "서버 열 순서" 값을 0으로 설정해야 합니다. 또한 "종결자" 및 "열 데이터 정렬" 필드를 ""(NULL)로 설정해야 합니다.
실제 열 이름은 필요하지 않지만 "서버 열 이름" 값에는 비블랭크 문자열이 필요합니다. 나머지 형식 필드는 기본값으로 둡니다.
다음 예제는
myTestSkipCol테이블의 기본 서식 파일에서도 파생됩니다. 0 또는 NULL이어야 하는 값은 굵게 표시됩니다.9.0 3 1 SQLCHAR 0 7 "\t" 1 Col1 "" 2 SQLCHAR 00""0 Col2 "" 3 SQLCHAR 0 100 "\r\n" 3 Col3 SQL_Latin1_General_CP1_CI_AS
예시
이 항목의 앞부분에서 설명한 "샘플 테이블과 데이터 파일"에서 생성된 샘플 테이블 및 샘플 데이터 파일을 기반으로 다음 예제를 제시합니다.
BULK INSERT 사용
이 예제에서는 이 항목의 앞부분에 있는 "비 XML 서식 파일 수정 방법"에서 만든 수정된 비 XML 서식 파일 중 하나를 사용하여 작동합니다. 이 예제에서는 수정된 서식 파일의 이름을 C:\myTestSkipCol2.fmt로 합니다. :
SQL Server Management Studio 쿼리 편집기에서 다음 코드를 실행하여 myTestSkipCol2.dat 데이터 파일을 대량으로 가져오기 위해 BULK INSERT를 사용합니다.
USE AdventureWorks2012;
GO
BULK INSERT myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO
XML 서식 파일 사용
XML 서식 파일을 사용하면 bcp 명령이나 BULK INSERT 문을 사용하여 테이블로 직접 가져올 때 열을 건너뛸 수 없습니다. 그러나 테이블의 마지막 열을 제외한 모든 열로 가져올 수 있습니다. 마지막 열을 제외한 모든 열을 건너뛰어야 하는 경우 데이터 파일에 포함된 열만 포함된 대상 테이블의 뷰를 만들어야 합니다. 그런 다음 해당 파일에서 뷰로 데이터를 대량으로 가져올 수 있습니다.
XML 서식 파일을 사용하여 OPENROWSET(BULK...)을 사용하여 테이블 열을 건너뛰려면 다음과 같이 선택 목록 및 대상 테이블에서 열의 명시적 목록을 제공해야 합니다.
삽입하다...<> COLUMN_LIST OPENROWSET에서 column_list> 선택<(BULK...)
기본 XML 서식 파일 만들기
수정된 형식 파일의 예는 이 항목의 앞부분에 myTestSkipCol 있는 "샘플 테이블 및 데이터 파일"에서 만든 샘플 테이블 및 데이터 파일을 기반으로 합니다. 다음 bcp 명령은 테이블에 대한 myTestSkipCol 기본 XML 서식 파일을 만듭니다.
bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T
결과 기본 비 XML 서식 파일은 다음과 같이 데이터 파일 필드와 테이블 열 간의 일대일 대응을 설명합니다.
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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)을 참조하세요.
예시
이 섹션의 예제에서는 이 항목의 앞부분에 myTestSkipCol 있는 "샘플 테이블 및 myTestSkipCol2.dat 데이터 파일"에서 만든 샘플 테이블 및 샘플 데이터 파일을 사용합니다. 데이터를 myTestSkipCol2.dat에서 myTestSkipCol 테이블로 가져오기 위해서는 다음 수정된 XML 형식 파일인 myTestSkipCol2-x.xml를 사용합니다. 이 항목의 앞부분에 있는 "기본 XML 서식 파일 만들기"에서 만든 서식 파일을 기반으로 합니다.
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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>
OPENROWSET 사용(BULK...)
다음 예제에서는OPENROWSET 대량 행 집합 공급자 및 myTestSkipCol2.xml 서식 파일을 사용합니다. 이 예제에서는 myTestSkipCol2.dat 데이터 파일을 myTestSkipCol 테이블로 대량으로 가져옵니다. 문에는 필요에 따라 선택 목록뿐 아니라 대상 테이블에도 있는 열의 명시적 목록이 포함됩니다.
SQL Server Management Studio 쿼리 편집기에서 다음 코드를 실행합니다.
USE AdventureWorks2012;
GO
INSERT INTO myTestSkipCol
(Col1,Col3)
SELECT Col1,Col3
FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat',
FORMATFILE='C:\myTestSkipCol2.Xml'
) as t1 ;
GO
보기에서 BULK IMPORT 사용
다음 예제에서는 myTestSkipCol 테이블에 v_myTestSkipCol을(를) 만듭니다. 이 뷰는 두 번째 테이블 열(Col2)을 건너뜁니다. 이 예제에서는 BULK INSERT를 사용하여 myTestSkipCol2.dat 데이터 파일을 이 뷰로 가져옵니다.
SQL Server Management Studio 쿼리 편집기에서 다음 코드를 실행합니다.
CREATE VIEW v_myTestSkipCol AS
SELECT Col1,Col3
FROM myTestSkipCol;
GO
USE AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO
또한 참조하십시오
bcp 유틸리티BULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server)서식 파일을 사용하여 테이블 열을 Data-File 필드로 매핑(SQL Server)서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)