대량 삽입 작업

대량 삽입 작업

 

안녕하세요 강성욱 입니다.(www.sqltagl.org, www.sqler.com, http://blog.naver.com/jevida)

 

이번 시간에는 [대량 삽입 작업]에 대해서 알아 보도록 하겠습니다.

 

대량 삽입작업은 SQL Server의 [BULK INSERT], [BCP.EXE]와 같은 텍스트 형식의 데이터 파일을 SQL Server의 테이블로 빠르게 입력할 때 이용되는 개체 입니다.

대량 삽입 작업은 [데이터 흐름 엔진]을 사용하지 않음으로 단순희 원본 텍스트 파일에서 테이블로 로딩만 가능하며 가공이나 집계, 편집, 변환 기능을 구현할 수가 없습니다.

 

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

 

[제어 흐름] 탭에서 [대량 삽입 태스크]를 드래그 앤 드롭으로 추가 합니다.

 

[대량 삽입 태스크]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

 

[일반] 탭에서는 태스크의 이름과 설명을 입력 합니다.

 

[연결] 탭에서는 원본 및 대상, 원본의 서식을 설정 합니다.

대상 연결

• Connection – 원본 텍스트 파일의 데이터를 저장할 데이터베이스 연결을 지정
• DestinationTable – 원본 텍스트 파일을 저장할 테이블을 지정

서식

• Format – 대량 삽입 작업을 위한 서식의 형태를 선택.
  • 파일 사용 – 서식이 지정된 파일을 사용 합니다. (BULK INSERT 명령의 WITH (FORMATFILE) 옵션과 동일)
  • 지정 – 직접 RowDelimiter 및 ColumnDelimiter의 속성을 지정.
• RowDelimeter – 행 구분자를 설정.
• ColumnDelimiter – 열 구분자를 설정.

원본 연결

• 파일 – 원본으로 사용될 텍스트를 지정.

 

[옵션] 탭에서는 CodePage나 FileType 등과 같이 고급 옵션을 설정 할 수 있습니다.

고급 옵션

• CodePage
  • ACP – char, varchar 또는 text 데이터 형식의 열은 ANSI/Microsoft Windows 코드 페이지(ISO 1252)에서 SQL Server 코드 페이지로 변환.
  • OEM – char, varchar 또는 text 데이터 형식의 열은 시스템 OEM 코드 페이지에서 SQL Server 코드 페이지로 변환됩니다.
  • RAW – 다른 코드 페이지로의 변호나이 이루어지지 않는 가장 빠른 옵션
  • 기타 코드 페이지 – 특정 코드(예: 949, 1252)로 변환.
• DataFileType
  • Char – 일반 문자 형식
  • Native – 네이티브(데이터베이스) 데이터 형식. BCP 유틸리티로 SQL Server에서 데이터를 대량 로드하여 네이티브 데이터 파일을 만들 수 있습니다. 네이티브 형식은 char형식보다 성능이 좋음.
  • Widechar – 유니코드 문자 형식
  • Widenative – 데이터가 유니코드로 저장되는 네이티브(데이터베이스) 형식. Char, varchar alc Text열은 제외. 데이터 파일에 ANSI 확장 문자가 포함되어 있으면 widenative로 지정해야 한다.
• BatchSize – 일괄 처리의 행 수를 지정합니다. BatchSize를 0으로 설정하면 데이터가 단일 일괄 처리로 로드.
• LastRow – 삽입할 마지막 행의 번호를 지정. 기본값은 0이며 이는 지정한 데이터 파일의 마지막 행을 가리킵니다.
• FirstRow – 삽입할 첫 번째 행의 번호를 지정. 기본ㄱ밧은 1이며 이는 지정한 데이터 파일의 첫 번째 행입니다.

옵션

• Option
  • CHECK 제약 조건 – 대량삽입 작업 중에 대상 테이블 또는 뷰의 모든 제약 조건을 확인 하도록 지정 합니다. CHECK 제약 조건 옵션을 지정하지 않으면 모든 CHECK 제약 조건이 무시 됩니다. Unique, Primary Key, Foreign Key, Not null 조건은 항상 적용 됩니다.
  • Null 유지 – 삽입된 열에 기본값이 지정되지 않도록 하며 빈 열인 경우 Null값을 유지하도록 지정.
  • ID 삽입 기능 – Identity 열에 데이터를 입력 할 경우 데이터 파일의 ID 값이 대상 테이블의 ID열에 그대로 입력되도록 지정. ID 삽입 기능을 선택하지 않는 경우 이 열의 ID값은 확인하지만 가져오지는 않습니다.
  • 테이블 잠금 – 대량 삽입 작업이 진행되는 동안 테이블 수준 잠금을 보유하도록 지정. 테이블에 인덱스가 없고 TABLOCK이 지정되어 있으면 여러 클라이언트가 동시에 테이블 로드 가능. 기본적으로 잠금 동작은 대상 테이블의 table lock on bulk load 테이블 옵션에 의해 결졍됩니다. 대량 로드 작업중 테이블 잠금을 보유하면 잠금 경합이 줄어들고 성능이 크게 향상됩니다.
  • 트리거 실행 – 대상 테이블에 정의된 삽입 트리거가 실행되도록 지정. 트리거가 대상 테이블의 INSERT 작업에 대해 정의되어 있는 경우에는 모든 입력 데이터에 대해 발생.
• SorteData – 데이터 파일의 데이터 정렬 방법을 지정. 로드된 데이터가 테이블의 클러스터형 인덱스와 동일한 순서로 정렬되어 있으면 대량 삽입 작업의 성능이 향상 됩니다. 데이터 파일을 다른 순서로 정렬하거나 테이블에 클러스터형 인덱스가 없으면 ORDER 절이 무시 됩니다. SortedData 속성에서 지정된 열 이름은 대상 테이블의 열 이어야 합니다.
• MaxErrors – 허용되는 최대 오류 수 지정. 대량 삽입 작업으로 가져올 수 없는 각 행은 무시되고 하나의 오류로 계산됨. 기본값은 0.

 

[식] 탭에서는 다양한 식을 정의 할 수 있습니다.

 

[편집]이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

 

패키지 작업이 완료 되었으면 SSMS를 실행하여 정상적으로 BLUK INSERT 가 되었는지 확인 합니다.

 

단순히 데이터를 로딩하는 작업이라면 [대량 삽입 작업]이 매우 효과적 입니다.