티스토리 뷰

💼 정보 ver1.0

Bulk Insert 대량 데이터 처리

James Wetzel 2013. 5. 7. 15:01
728x90
반응형

명령문

comm.CommandText = @"BULK INSERT " + tableName + " FROM '" + dataFile + "' WITH (FORMATFILE='" + bulkPath + "')";



BULK INSERT(Transact-SQL)

SQL Server 2008
이 항목은 아직 평가되지 않았습니다.- 이 항목 평가

데이터 파일을 사용자가 지정한 형식으로 데이터베이스 테이블이나 뷰로 가져옵니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문
BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
      [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
      [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )]
인수
database_name

지정한 테이블이나 뷰가 상주하는 데이터베이스 이름입니다. 지정하지 않으면 기본값은 현재 데이터베이스입니다.

schema_name

테이블 또는 뷰 스키마의 이름입니다. 대량 가져오기 작업을 수행하는 사용자의 기본 스키마가 지정한 테이블이나 뷰의 스키마인 경우에는schema_name을 지정하지 않아도 됩니다. schema를 지정하지 않은 경우 대량 가져오기 작업을 수행하는 사용자의 기본 스키마가 지정된 테이블이나 뷰와 다르면 SQL Server에서 오류 메시지를 반환하고 대량 가져오기 작업이 취소됩니다.

table_name

데이터를 대량으로 가져올 테이블 또는 뷰의 이름입니다. 모든 열이 동일한 기본 테이블을 참조하는 뷰만 사용할 수 있습니다. 데이터를 뷰에 로드할 때의 제한 사항에 대한 자세한 내용은 INSERT(Transact-SQL)를 참조하십시오.

' data_file '

지정한 테이블이나 뷰로 가져올 데이터가 포함된 데이터 파일의 전체 경로입니다. BULK INSERT는 디스크(예: 네트워크, 플로피 디스크, 하드 디스크 등)에서 데이터를 가져올 수 있습니다.

data_file은 SQL Server가 실행 중인 서버에서 유효한 경로를 지정해야 합니다. data_file이 원격 파일일 경우 UNC(Universal Naming Convention) 이름을 지정합니다. UNC 이름은 \\Systemname\ShareName\Path\FileName 형식으로 입력합니다. 예를 들면 \\SystemX\DiskZ\Sales\update.txt와 같습니다.

BATCHSIZE =batch_size

일괄 처리에 포함될 행 수를 지정합니다. 모든 일괄 처리는 하나의 트랜잭션으로 서버에 복사됩니다. 이 작업이 실패하면 SQL Server는 모든 일괄 처리에 대한 트랜잭션을 커밋하거나 롤백합니다. 기본적으로 지정된 데이터 파일의 모든 데이터는 하나의 일괄 처리입니다. 성능 고려 사항에 대한 자세한 내용은 이 항목의 뒷부분에 나오는 "주의"를 참조하십시오.

자세한 내용은 대량 가져오기를 위한 일괄 처리 관리를 참조하십시오.

CHECK_CONSTRAINTS

대량 가져오기 작업 중 대상 테이블 또는 뷰의 모든 제약 조건을 검사하도록 지정합니다. CHECK_CONSTRAINTS 옵션을 지정하지 않으면 모든 CHECK 및 FOREIGN KEY 제약 조건이 무시되고 작업 이후 테이블의 제약 조건은 신뢰할 수 없는 것으로 표시됩니다.

참고 참고

UNIQUE, PRIMARY KEY 및 NOT NULL 제약 조건은 항상 적용됩니다.

특정 지점에서는 전체 테이블의 제약 조건을 확인해야 합니다. 대량 가져오기 작업을 수행하기 전에 테이블이 비어 있지 않으면 제약 조건의 유효성을 다시 검사하는 비용이 증분 데이터에 CHECK 제약 조건을 적용하는 비용을 초과할 수 있습니다.

입력 데이터에 제약 조건을 위반하는 행이 포함된 경우에는 제약 조건을 사용하지 않을 수 있습니다(기본 동작). CHECK 제약 조건을 사용하지 않으면 데이터를 가져온 다음 Transact-SQL 문을 사용하여 잘못된 데이터를 제거할 수 있습니다.

참고 참고

MAXERRORS 옵션은 제약 조건 확인에 적용되지 않습니다.

참고 참고

SQL Server 2005 이상 버전에서 BULK INSERT는 새 데이터 유효성 검사 및 데이터 검사를 강제로 실행합니다. 이로 인해 데이터 파일의 잘못된 데이터에서 기존 스크립트를 실행하면 오류가 발생할 수 있습니다.

자세한 내용은 대량 가져오기 작업으로 제약 조건 검사 제어를 참조하십시오.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

데이터 파일에서 데이터의 코드 페이지를 지정합니다. CODEPAGE는 문자 값이 127보다 크거나 32보다 작은 char, varchar 또는 text 열이 데이터에 포함된 경우에만 적합합니다.

참고 참고

서식 파일의 각 열에 대해 데이터 정렬 이름을 지정하는 것이 좋습니다.

CODEPAGE 값

설명

ACP

char , varchar 또는 text 데이터 형식의 열은 ANSI/Microsoft Windows 코드 페이지(ISO 1252)에서 SQL Server 코드 페이지로 변환됩니다.

OEM(기본값)

char , varchar 또는 text 데이터 형식의 열은 시스템 OEM 코드 페이지에서 SQL Server 코드 페이지로 변환됩니다.

RAW

다른 코드 페이지로의 변환이 이루어지지 않는 가장 빠른 옵션입니다.

code_page

특정 코드 페이지 번호(예: 850)입니다.

중요 정보 중요
SQL Server는 코드 페이지 65001(UTF-8 인코딩)을 지원하지 않습니다.

자세한 내용은 서로 다른 데이터 정렬 간의 데이터 복사를 참조하십시오.

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

BULK INSERT에서 지정된 데이터 파일 유형 값을 사용하여 가져오기 작업을 수행하도록 지정합니다.

DATAFILETYPE 값

모든 데이터 표시 형식

char(기본값)

문자 형식

자세한 내용은 데이터를 가져오거나 내보낼 때 문자 형식 사용을 참조하십시오.

native

네이티브(데이터베이스) 데이터 형식. bcp 유틸리티로 SQL Server에서 데이터를 대량으로 가져와 네이티브 데이터 파일을 만듭니다.

네이티브 값은 char 값을 대체하여 보다 뛰어난 성능을 제공합니다.

자세한 내용은 네이티브 형식을 사용하여 데이터 가져오기 및 내보내기를 참조하십시오.

widechar

유니코드 문자

자세한 내용은 유니코드 문자 형식을 사용하여 데이터 가져오기 및 내보내기를 참조하십시오.

widenative

데이터가 유니코드로 저장되는 네이티브(데이터베이스) 데이터 형식입니다. char, varchar 및 text 열은 제외됩니다. bcp 유틸리티를 사용하여 SQL Server에서 데이터를 대량으로 가져와 widenative 데이터 파일을 만듭니다.

widenative 값은 widechar 값을 대체하여 보다 뛰어난 성능을 제공합니다. 데이터 파일에 ANSI 확장 문자가 포함되어 있으면widenative를 지정하십시오.

자세한 내용은 데이터를 가져오거나 내보내기 위해 유니코드 네이티브 형식 사용을 참조하십시오.

FIELDTERMINATOR ='field_terminator'

char 및 widechar 데이터 파일에 사용할 필드 종결자를 지정합니다. 기본 필드 종결자는 \t(탭 문자)입니다. 자세한 내용은 필드 및 행 종결자 지정을 참조하십시오.

FIRSTROW =first_row

로드할 첫 번째 행의 번호를 지정합니다. 기본값은 지정한 데이터 파일의 첫 번째 행입니다. FIRSTROW는 1부터 시작합니다.

참고 참고

FIRSTROW 특성은 열 머리글을 건너뛰기 위해 제공된 것이 아닙니다. BULK INSERT 문에서는 머리글 건너뛰기가 지원되지 않습니다. 행을 건너뛰면 SQL Server 데이터베이스 엔진은 필드 종결자만 확인하며 건너뛴 행의 필드에 있는 데이터의 유효성을 검사하지 않습니다.

FIRE_TRIGGERS

대량 가져오기 작업 중에 대상 테이블에서 정의된 삽입 트리거가 실행되도록 지정합니다. 트리거가 대상 테이블의 INSERT 작업에 대해 정의되면 완료된 모든 일괄 처리에 대해 발생합니다.

FIRE_TRIGGERS를 지정하지 않으면 삽입 트리거가 실행되지 않습니다.

자세한 내용은 데이터 대량 가져오기 작업 시의 트리거 실행 제어를 참조하십시오.

FORMATFILE ='format_file_path'

서식 파일의 전체 경로를 지정합니다. 서식 파일이란 동일한 테이블이나 뷰에서 bcp 유틸리티를 사용하여 생성된 저장 응답을 포함하는 데이터 파일입니다. 다음과 같은 경우에 서식 파일이 사용됩니다.

  • 데이터 파일의 열이 테이블 또는 뷰보다 많거나 적을 경우

  • 열 순서가 다를 경우

  • 열 구분 기호가 다를 경우

  • 데이터 서식에 기타 변경 내용이 있을 경우. 일반적으로 서식 파일은 bcp 유틸리티를 사용하여 만들고 필요에 따라 텍스트 편집기로 수정합니다. 자세한 내용은 bcp 유틸리티를 참조하십시오.

KEEPIDENTITY

가져온 데이터 파일의 ID 값이 ID 열에 사용되도록 지정합니다. KEEPIDENTITY를 지정하지 않는 경우 이 열의 ID 값을 확인하지만 가져오지는 않습니다. SQL Server에서는 테이블 생성 중에 지정된 초기값 및 증가값에 따라 고유한 값을 자동으로 할당합니다. 데이터 파일에 테이블이나 뷰의 ID 열 값이 포함되지 않은 경우 서식 파일을 사용하여 데이터를 가져올 때 테이블이나 뷰의 ID 열을 생략하도록 지정합니다. SQL Server에서는 해당 열에 고유한 값을 자동으로 할당합니다. 자세한 내용은 DBCC CHECKIDENT(Transact-SQL)를 참조하십시오.

ID 값 유지에 대한 자세한 내용은 데이터 대량 가져오기 중 ID 값 유지를 참조하십시오.

KEEPNULLS

대량 가져오기 작업 시 삽입된 열에 기본값이 지정되지 않도록 하고, 빈 열이 Null 값을 보유하도록 지정합니다. 자세한 내용은 대량 가져오기 수행 중 Null 유지 또는 기본값 사용을 참조하십시오.

KILOBYTES_PER_BATCH = kilobytes_per_batch

kilobytes_per_batch처럼 일괄 처리당 데이터 용량의 근사치(KB)를 지정합니다. 기본적으로 KILOBYTES_PER_BATCH는 알 수 없습니다. 성능 고려 사항에 대한 자세한 내용은 이 항목의 뒷부분에 나오는 "주의"를 참조하십시오.

자세한 내용은 대량 가져오기를 위한 일괄 처리 관리를 참조하십시오.

LASTROW=last_row

로드할 마지막 행의 번호를 지정합니다. 기본값은 0이며 지정한 데이터 파일의 마지막 행을 가리킵니다.

MAXERRORS = max_errors

대량 가져오기 작업을 취소하기 전에 데이터에서 허용되는 최대 구문 오류 수를 지정합니다. 대량 가져오기 작업으로 가져올 수 없는 각 행은 무시되고 하나의 오류로 계산됩니다. max_errors를 지정하지 않으면 기본값은 10입니다.

참고 참고

MAX_ERRORS 옵션은 제약 조건 확인이나 money 및 bigint 데이터 형식 변환에 적용되지 않습니다.

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

데이터 파일의 데이터 정렬 방법을 지정합니다. 가져올 데이터를 테이블의 클러스터형 인덱스(있는 경우)에 따라 정렬하면 대량 가져오기 성능이 향상됩니다. 데이터 파일을 클러스터형 인덱스 키와 다른 순서로 정렬하거나 테이블에 클러스터형 인덱스가 없으면 ORDER 절이 무시됩니다. 지정한 열 이름은 대상 테이블에서 올바른 열 이름이어야 합니다. 기본적으로 대량 삽입 작업은 데이터 파일이 정렬되지 않았음을 전제로 합니다. 대량 가져오기 작업을 최적화하기 위해 SQL Server에서는 가져온 데이터가 정렬되어 있는지도 확인합니다.

자세한 내용은 데이터 대량 가져오기 중 정렬 순서 제어를 참조하십시오.

n

복수 열을 지정할 수 있음을 나타내는 자리 표시자입니다.

ROWS_PER_BATCH =rows_per_batch

데이터 파일에 있는 대략적인 데이터 행 수를 나타냅니다.

기본적으로 데이터 파일의 모든 데이터는 단일 트랜잭션으로 서버에 전송되며 일괄 처리의 행 수는 쿼리 최적화 프로그램에 알려지지 않습니다. ROWS_PER_BATCH를 0보다 큰 값으로 지정하면 서버에서 이 값을 사용하여 대량 가져오기 작업을 최적화합니다. ROWS_PER_BATCH에 지정된 값은 실제 행 수와 대략적으로 동일해야 합니다. 성능 고려 사항에 대한 자세한 내용은 이 항목의 뒷부분에 나오는 "주의"를 참조하십시오.

자세한 내용은 대량 가져오기를 위한 일괄 처리 관리를 참조하십시오.

ROWTERMINATOR ='row_terminator'

char 및 widechar 데이터 파일에 사용할 행 종결자를 지정합니다. 기본 행 종결자는 \r\n(줄바꿈 문자)입니다. 자세한 내용은 필드 및 행 종결자 지정을 참조하십시오.

TABLOCK

대량 가져오기 작업이 진행되는 동안 테이블 수준 잠금을 획득하도록 지정합니다. 테이블에 인덱스가 없고 TABLOCK이 지정되어 있으면 여러 클라이언트가 동시에 테이블을 로드할 수 있습니다. 기본적으로 잠금 동작은 table lock on bulk load 테이블 옵션에 의해 결정됩니다. 대량 가져오기 작업이 진행되는 동안에만 잠금을 보유하면 테이블에 대한 잠금 경합이 줄어들고 이 경우 성능이 크게 향상됩니다. 성능 고려 사항에 대한 자세한 내용은 이 항목의 뒷부분에 나오는 "주의"를 참조하십시오.

자세한 내용은 대량 가져오기를 위한 잠금 동작 제어를 참조하십시오.

ERRORFILE ='file_name'

형식 오류가 있어 OLE DB 행 집합으로 변환할 수 없는 행을 수집하는 데 사용되는 파일을 지정합니다. 이러한 행은 데이터 파일에서 "있는 그대로" 이 오류 파일에 복사됩니다.

오류 파일은 명령이 실행될 때 생성됩니다. 파일이 이미 있으면 오류가 발생합니다. 또한 확장명이 .ERROR.txt인 제어 파일이 생성됩니다. 이 파일은 오류 파일의 각 행을 참조하여 오류를 진단합니다. 오류를 해결하는 즉시 데이터를 로드할 수 있습니다.

주의

BULK INSERT 문, INSERT ... SELECT * FROM OPENROWSET(BULK...) 문 및 bcp 명령을 비교하려면 대량 가져오기 및 내보내기 작업 정보를 참조하십시오.

대량 가져오기를 위한 데이터 준비 방법(예: CSV 데이터 파일에서 데이터를 가져오는 데 필요한 요구 사항)은 대량 내보내기 또는 가져오기를 위한 데이터 준비를 참조하십시오.

사용자 정의 트랜잭션 내에서 BULK INSERT 문을 실행할 수 있습니다. 여러 일괄 처리를 사용하여 테이블이나 뷰에 데이터를 가져오기 위해 BULK INSERT 문과 BATCHSIZE 절을 사용하는 사용자 정의 트랜잭션을 롤백하면 SQL Server에 전송된 모든 일괄 처리가 롤백됩니다.

대량 가져오기로 수행된 행 삽입 작업이 트랜잭션 로그에 기록되는 경우에 대한 자세한 내용은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하십시오.

SQL Server 2005 이상 버전에서 BULK INSERT는 파일에서 읽은 데이터에 새롭고 보다 엄격한 데이터 유효성 검사 및 데이터 검사를 강제로 실행합니다. 이로 인해 기존 스크립트가 잘못된 데이터에서 실행되면 오류가 발생할 수 있습니다. 예를 들어 BULK INSERT는 다음을 확인합니다.

  • float 또는 real 데이터 형식의 네이티브 표시는 유효합니다.

  • 유니코드 데이터의 길이가 짝수 바이트인지 여부

이전 버전의 SQL Server에서 대량으로 가져올 수 있었던 잘못된 데이터 형식은 이제 로드되지 않을 수 있습니다. SQL Server의 이전 버전에서는 클라이언트가 잘못된 데이터에 액세스하지 않으면 오류가 발생하지 않습니다. 보다 엄격한 유효성 검사를 수행하면 대량 가져오기 이후 데이터를 쿼리할 때 발생할 수 있는 예상치 못한 문제가 최소화됩니다.

성능 고려 사항

단일 일괄 처리에서 플러시될 페이지 수가 내부 임계값을 초과하면 버퍼 풀의 전체 검색이 일괄 처리를 커밋할 때 플러시할 페이지를 식별할 수 있습니다. 이 전체 검색으로 대량 가져오기 성능이 저하될 수 있습니다. 대용량 버퍼 풀이 느린 I/O 하위 시스템과 결합할 때 내부 임계값이 초과될 수 있습니다. 대규모 컴퓨터에서 버퍼 오버플로를 방지하려면 대량 최적화를 제거하는 TABLOCK 힌트를 사용하지 않거나 대량 최적화를 유지하는 보다 작은 크기의 일괄 처리를 사용합니다.

컴퓨터마다 다르기 때문에 데이터 로드로 다양한 일괄 처리 크기를 테스트하여 가장 적합한 일괄 처리를 찾는 것이 좋습니다.

SQLXML 문서 대량 내보내기 또는 가져오기

SQLXML 데이터를 대량으로 내보내거나 가져오려면 서식 파일에서 다음 데이터 형식 중 하나를 사용합니다.

데이터 형식

영향

SQLCHAR 또는 SQLVARYCHAR

데이터를 클라이언트 코드 페이지나 데이터 정렬에 포함된 코드 페이지로 보냅니다. 결과는 서식 파일을 지정하지 않고 DATAFILETYPE ='char'를 지정하는 것과 같습니다.

SQLNCHAR 또는 SQLNVARCHAR

데이터를 유니코드로 보냅니다. 결과는 서식 파일을 지정하지 않고 DATAFILETYPE = 'widechar'를 지정하는 것과 같습니다.

SQLBINARY 또는 SQLVARYBIN

데이터를 변환하지 않고 보냅니다.

String 형식에서 Decimal 형식으로의 변환

SQL Server 2005 이상 버전에서 BULK INSERT에 사용되는 String 형식에서 Decimal 형식으로의 변환은 Transact-SQLCONVERT 함수와 동일한 규칙을 따르며 과학적 표기법을 사용하는 숫자 값을 나타내는 문자열을 거부합니다. 따라서 BULK INSERT는 이러한 문자열을 잘못된 값으로 처리하고 변환 오류를 보고합니다.

참고 참고

SQL Server 버전 7.0 및 SQL Server 2000에서 BULK INSERT는 과학적 표기법을 사용한 숫자 값을 나타내는 문자열을 변환하기 위한 String 형식에서 Decimal 형식으로의 변환을 지원합니다.

이 작업을 수행하려면 서식 파일을 사용하여 과학적 표기법 float 데이터를 Decimal 열로 대량 가져옵니다. 서식 파일에서 명시적으로 열을 real 또는 float 데이터로 설명합니다. 두 데이터 형식에 대한 자세한 내용은 float 및 real(Transact-SQL)을 참조하십시오.

참고 참고

서식 파일은 real 데이터를 SQLFLT4 데이터 형식으로, float 데이터를 SQLFLT8 데이터 형식으로 나타냅니다. XML 서식 파일에 대한 자세한 내용은 XML 서식 파일의 스키마 구문을 참조하십시오. 비 XML 서식 파일에 대한 자세한 내용은 bcp를 사용하여 파일 저장 유형 지정을 참조하십시오.

과학적 표기법을 사용하는 숫자 값 가져오기 예

이 예에서는 다음 테이블을 사용합니다.

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

사용자가 t_float 테이블로 데이터를 대량 가져오려고 합니다. 데이터 파일 C:\t_float-c.dat에는 과학적 표기법 float 데이터가 들어 있습니다. 예를 들면 다음과 같습니다.

8.0000000000000002E-28.0000000000000002E-2

그러나 두 번째 열 c2에서 decimal 데이터 형식을 사용하므로 BULK INSERT는 이 데이터를 직접 t_float로 가져올 수 없습니다. 따라서 서식 파일이 필요합니다. 서식 파일은 열 c2의 과학적 표기법 float 데이터를 Decimal 형식으로 매핑해야 합니다.

다음 서식 파일은 SQLFLT8 데이터 형식을 사용하여 두 번째 데이터 필드를 두 번째 열로 매핑합니다.

<?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="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

이 서식 파일(C:\t_floatformat-c-xml.xml)을 사용하여 테스트 데이터를 테스트 테이블로 가져오려면 다음 Transact-SQL 문을 실행합니다.

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO
사용 권한

INSERT 및 ADMINISTER BULK OPERATIONS 권한이 필요합니다. 또한 다음 중 하나 이상이 적용되는 경우에는 ALTER TABLE 권한이 필요합니다.

  • 제약 조건이 있으며 CHECK_CONSTRAINTS 옵션을 지정하지 않았습니다.

    참고 참고

    기본적으로 제약 조건은 사용되지 않습니다. 명시적으로 제약 조건을 확인하려면 CHECK_CONSTRAINTS 옵션을 사용하십시오.

  • 트리거가 있으며 FIRE_TRIGGER 옵션을 지정하지 않았습니다.

    참고 참고

    기본적으로 트리거는 실행되지 않습니다. 명시적으로 트리거를 발생시키려면 FIRE_TRIGGER 옵션을 사용하십시오.

  • KEEPIDENTITY 옵션을 사용하여 데이터 파일에서 ID 값을 가져올 수 있습니다.

보안 계정 위임(가장)

SQL Server 사용자가 Windows 인증을 사용하여 로그인한 경우에는 SQL Server 프로세스의 보안 프로필과 관계없이 사용자 계정에서 액세스할 수 있는 파일만 읽을 수 있습니다.

sqlcmd 또는 osql을 사용하는 BULK INSERT 문을 실행하는 경우 한 컴퓨터에서 두 번째 컴퓨터의 SQL Server에 데이터를 삽입하고 UNC 경로를 사용하여 세 번째 컴퓨터에서 data_file을 지정하면 4861 오류가 발생할 수 있습니다.

이러한 오류를 해결하려면 SQL Server 인증을 사용하고 SQL Server 프로세스 계정의 보안 프로필을 사용하는 SQL Server 로그인을 지정하거나 보안 계정 위임을 사용하도록 Windows를 구성하십시오. 위임용으로 사용자 계정이 신뢰될 수 있도록 설정하는 방법은 Windows 도움말을 참조하십시오.

이와 관련된 보안 고려 사항 및 BULK INSERT 사용에 대한 기타 보안 고려 사항은 BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기를 참조하십시오.

예

1. 파이프를 사용하여 파일에서 데이터 가져오기

다음 예에서는 필드 종결자로 파이프(|)를 사용하고 행 종결자로 |\n을 사용하여 지정한 데이터 파일에서 AdventureWorks.Sales.SalesOrderDetail 테이블로 주문 세부 정보를 가져옵니다.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

2. FIRE_TRIGGERS 인수 사용

다음 예에서는 FIRE_TRIGGERS 인수를 지정합니다.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

3. 행 종결자로 줄 바꿈 사용

다음 예에서는 UNIX 출력 같은 행 종결자로 줄 바꿈을 사용하는 파일을 가져옵니다.

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

추가 예

다른 BULK INSERT 예는 다음 항목에 나와 있습니다.

변경 내역

업데이트된 내용

"주의" 섹션에 성능 고려 사항을 추가했습니다.

FIRSTROW 옵션에 대한 설명을 업데이트했습니다.

code_page에 대한 설명을 업데이트했습니다.




서식 파일 만들기

SQL Server 2008
이 항목은 아직 평가되지 않았습니다.- 이 항목 평가

SQL Server 테이블에 데이터를 대량으로 가져오거나 테이블의 데이터를 대량으로 내보내는 경우 서식 파일을 사용하면 해당 테이블을 기준으로 데이터 파일에 각 필드에 대한 서식 정보를 저장할 수 있습니다. 서식 파일을 사용하면 다른 데이터 형식과 맞추기 위한 편집 작업이 거의 필요 없는 데이터 파일을 작성하거나 다른 소프트웨어의 데이터 파일을 읽는 작업을 유연하게 수행할 수 있습니다.

SQL Server 2005 이상 버전에서는 두 유형의 서식 파일, 즉 비 XML 서식 파일과 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 이전 버전의 SQL Server에서 원래 지원했던 서식 파일입니다.

일반적으로 XML 서식 파일과 비 XML 서식 파일은 서로 전환이 가능하지만 새 서식 파일에는 비 XML 서식 파일에 비해 여러 가지 장점이 있는 XML 구문을 사용하는 것이 좋습니다. 자세한 내용은 서식 파일 소개를 참조하십시오.

참고 참고

서식 파일을 읽는 데 사용되는 bcp 유틸리티(Bcp.exe)의 버전은 서식 파일을 만드는 데 사용되는 버전 이상이어야 합니다. 예를 들어 SQL Server 2008bcp는 SQL Server 2005bcp에서 생성된 버전 9.0 서식 파일을 읽을 수 있지만 SQL Server 2005bcp는 SQL Server 2008bcp에서 생성된 버전 10.0 서식 파일을 읽을 수 없습니다.

이 항목에서는 bcp 유틸리티를 사용하여 특정 테이블에 대한 서식 파일을 만드는 방법에 대해 설명합니다. 서식 파일은 지정된 데이터 형식 옵션(-n, -c, -w 또는-N)과 테이블 또는 뷰 구분 기호를 기반으로 합니다.

비 XML 서식 파일 만들기

bcp 명령을 사용하여 서식 파일을 만들려면 데이터 파일 경로 대신 format 인수를 지정하고 nul을 사용합니다. format 옵션에는 다음과 같이 -f 옵션도 필요합니다.

bcp table_or_view format nul -fformat_file_name

참고 참고

비 XML 서식 파일을 구분하기 위해 파일 이름 확장명으로 .fmt를 사용하는 것이 좋습니다(예: MyTable.fmt).

비 XML 서식 파일의 구조 및 필드에 대한 자세한 내용은 비 XML 서식 파일 이해를 참조하십시오.

예

이 섹션에는 bcp 명령을 사용하여 비 XML 서식 파일을 만드는 방법을 보여 주는 다음 예가 포함되어 있습니다.

  • 1. 네이티브 데이터용 비 XML 서식 파일 만들기

  • 2. 문자 데이터용 비 XML 서식 파일 만들기

  • 3. 유니코드 네이티브 데이터용 비 XML 서식 파일 만들기

  • 4. 유니코드 문자 데이터용 비 XML 서식 파일 만들기

다음 예에서는 AdventureWorks 예제 데이터베이스의 HumanResources.Department 테이블을 사용합니다. HumanResources.Department 테이블에는DepartmentID, Name, GroupName 및 ModifiedDate의 4개 열이 있습니다. 이 테이블의 정의에 대해서는 Department 테이블(AdventureWorks)을 참조하십시오.

참고 참고

Adventure Works Cycles는 데이터베이스 개념 및 시나리오를 설명하는 데 사용되는 가상 제조 회사입니다.

1. 네이티브 데이터용 비 XML 서식 파일 만들기

다음 예에서는 AdventureWorksHumanResources.Department 테이블에 대한 Department-n.xml XML 서식 파일을 만듭니다. 서식 파일은 네이티브 데이터 형식을 사용합니다. 생성된 서식 파일의 내용은 명령 뒤에 표시됩니다.

bcp 명령에는 다음 한정자가 포함됩니다.

한정자

설명

format nul -fformat_file

비 XML 서식 파일을 지정합니다.

-n

네이티브 데이터 형식을 지정합니다.

-T

bcp 유틸리티가 통합 보안을 사용하는 트러스트된 연결을 통해 SQL Server로 연결되도록 지정합니다. -T가 지정되어 있지 않을 경우 성공적으로 로그인하려면 -U와 -P를 지정해야 합니다.

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

bcp AdventureWorks.HumanResources.Department format nul -T -n -f Department-n.fmt

생성된 Department-n.fmt 서식 파일에는 다음 정보가 포함됩니다.

9.0
4
1       SQLSMALLINT   0       2       ""   1     DepartmentID                 ""
2       SQLNCHAR      2       100     ""   2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLDATETIME   0       8       ""   4     ModifiedDate                 ""

자세한 내용은 비 XML 서식 파일 이해를 참조하십시오.

2. 문자 데이터용 비 XML 서식 파일 만들기

다음 예에서는 AdventureWorksHumanResources.Department 테이블에 대한 Department.fmt XML 서식 파일을 만듭니다. 서식 파일은 문자 데이터 형식 및 기본이 아닌 필드 종결자(,)를 사용합니다. 생성된 서식 파일의 내용은 명령 뒤에 표시됩니다.

bcp 명령에는 다음 한정자가 포함됩니다.

한정자

설명

format nul -fformat_file

비 XML 서식 파일을 지정합니다.

-c

문자 데이터를 지정합니다.

-T

bcp 유틸리티가 통합 보안을 사용하는 트러스트된 연결을 통해 SQL Server로 연결되도록 지정합니다. -T가 지정되어 있지 않을 경우 성공적으로 로그인하려면 -U와 -P를 지정해야 합니다.

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

bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T

생성된 Department-c.fmt 서식 파일에는 다음 정보가 포함됩니다.

9.0
4
1       SQLCHAR       0       7       "\t"     1     DepartmentID                 ""
2       SQLCHAR       0       100     "\t"     2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\t"     3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       24      "\r\n"   4     ModifiedDate                 ""

자세한 내용은 비 XML 서식 파일 이해를 참조하십시오.

3. 유니코드 네이티브 데이터용 비 XML 서식 파일 만들기

HumanResources.Department 테이블을 위한 유니코드 네이티브 데이터용 비 XML 서식 파일을 만들려면 다음 명령을 사용합니다.

bcp AdventureWorks.HumanResources.Department format nul -T -N -f Department-n.fmt

유니코드 네이티브 데이터 사용 방법은 데이터를 가져오거나 내보내기 위해 유니코드 네이티브 형식 사용을 참조하십시오.

4. 유니코드 문자 데이터용 비 XML 서식 파일 만들기

기본 종결자를 사용하는 HumanResources.Department 테이블을 위한 유니코드 문자 데이터용 비 XML 서식 파일을 만들려면 다음 명령을 사용합니다.

bcp AdventureWorks.HumanResources.Department format nul -T -w -f Department-w.fmt

유니코드 문자 데이터 사용 방법은 유니코드 문자 형식을 사용하여 데이터 가져오기 및 내보내기를 참조하십시오.

XML 서식 파일 만들기

bcp 명령을 사용하여 서식 파일을 만들려면 데이터 파일 경로 대신 format 인수를 지정하고 nul을 사용합니다. 다음과 같이 format 옵션에는 항상 -f 옵션이 필요하며 XML 서식 파일을 만드는 경우 -x 옵션도 지정해야 합니다.

bcp table_or_view format nul -f format_file_name -x

참고 참고

XML 서식 파일을 구분하기 위해 파일 이름 확장명으로 .xml을 사용하는 것이 좋습니다(예: MyTable.xml).

XML 서식 파일의 구조 및 필드에 대한 자세한 내용은 XML 서식 파일 이해를 참조하십시오. 구문에 대한 정보는 XML 서식 파일의 스키마 구문을 참조하십시오. 예를 보려면 예제 XML 서식 파일을 참조하십시오.

예

이 섹션에는 bcp 명령을 사용하여 XML 서식 파일을 만드는 방법을 보여 주는 다음 예가 포함되어 있습니다.

  • 1. 문자 데이터용 XML 서식 파일 만들기

  • 2. 네이티브 데이터용 XML 서식 파일 만들기

다음 예에서는 AdventureWorks 예제 데이터베이스의 HumanResources.Department 테이블을 사용합니다. HumanResources.Department 테이블에는DepartmentID, Name, GroupName 및 ModifiedDate의 4개 열이 있습니다. 이 테이블의 정의에 대해서는 Department 테이블(AdventureWorks)을 참조하십시오.

참고 참고

Adventure Works Cycles는 데이터베이스 개념 및 시나리오를 설명하는 데 사용되는 가상 제조 회사입니다.

1. 문자 데이터용 XML 서식 파일 만들기

다음 예에서는 AdventureWorksHumanResources.Department 테이블에 대한 Department.xml XML 서식 파일을 만듭니다. 서식 파일은 문자 데이터 형식 및 기본이 아닌 필드 종결자(,)를 사용합니다. 생성된 서식 파일의 내용은 명령 뒤에 표시됩니다.

bcp 명령에는 다음 한정자가 포함됩니다.

한정자

설명

format nul -fformat_file -x

XML 서식 파일을 지정합니다.

-c

문자 데이터를 지정합니다.

-t ,

쉼표(,)를 필드 종결자로 지정합니다.

참고 참고
데이터 파일이 기본 필드 종결자(\t)를 사용하면 -t 스위치는 불필요합니다.

-T

bcp 유틸리티가 통합 보안을 사용하는 트러스트된 연결을 통해 SQL Server로 연결되도록 지정합니다. -T가 지정되어 있지 않을 경우 성공적으로 로그인하려면 -U와 -P를 지정해야 합니다.

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

bcp AdventureWorks.HumanResources.Department format nul -c -x -f Department-c..xml –t, -T

생성된 Department-c.xml 서식 파일에는 다음 XML 요소가 포함됩니다.

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

이 서식 파일의 구문에 대한 자세한 내용은 XML 서식 파일의 스키마 구문을 참조하십시오. 문자 데이터에 대한 자세한 내용은 데이터를 가져오거나 내보낼 때 문자 형식 사용을 참조하십시오.

2. 네이티브 데이터용 XML 서식 파일 만들기

다음 예에서는 AdventureWorksHumanResources.Department 테이블에 대한 Department-n.xml XML 서식 파일을 만듭니다. 서식 파일은 네이티브 데이터 형식을 사용합니다. 생성된 서식 파일의 내용은 명령 뒤에 표시됩니다.

bcp 명령에는 다음 한정자가 포함됩니다.

한정자

설명

format nul -fformat_file -x

XML 서식 파일을 지정합니다.

-n

네이티브 데이터 형식을 지정합니다.

-T

bcp 유틸리티가 통합 보안을 사용하는 트러스트된 연결을 통해 SQL Server로 연결되도록 지정합니다. -T가 지정되어 있지 않을 경우 성공적으로 로그인하려면 -U와 -P를 지정해야 합니다.

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

bcp AdventureWorks.HumanResources.Department format nul -x -f Department-n..xml -n -T

생성된 Department-n.xml 서식 파일에는 다음 XML 요소가 포함됩니다.

<?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="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

이 서식 파일의 구문에 대한 자세한 내용은 XML 서식 파일의 스키마 구문을 참조하십시오. 네이티브 데이터 사용 방법은 네이티브 형식을 사용하여 데이터 가져오기 및 내보내기를 참조하십시오.

테이블 열에 데이터 필드 매핑

bcp에서 만든 서식 파일은 모든 테이블 열에 대한 설명을 순서대로 제공합니다. 서식 파일을 수정하여 테이블 행을 다시 정렬하거나 생략할 수 있습니다. 이렇게 하면 테이블 열에 직접 매핑하지 않는 필드가 있는 데이터 파일에 대해 서식 파일을 사용자 지정할 수 있습니다. 자세한 내용은 다음 항목을 참조하십시오.

이 정보가 도움이 되었습니까?  




728x90
반응형
저작자표시 비영리 동일조건