엑셀 데이터를 대량으로 MSSQL의 테이블에 넣을 일이 생겼다.
일일이 INSERT 하기가 너무 귀찮아서 방법을 찾아보니,
MSSQL에서 제공하는 BULK 함수를 사용하면 손쉽게 입력이 가능하다는 사실을 알게되었다.
매우매우 간단하니 아래 내용을 참고해서 따라오길 바란다.
MSSQL CSV/EXCEL 파일 테이블 INSERT 방법
1. Excel 파일을 CSV 파일로 변환
- 엑셀 파일 > 다른이름으로 저장 > "CSV UTF-8(쉼표로 분리) (.csv)" 선택
엑셀 CSV 파일로 저장(다른이름으로 저장)
2. MSSQL Server에서 SQL 실행(BULK INSERT ~ )
SQL 상세 내용은 하단 참고
MSSQL CSV 파일 데이터 가져오기
Example1
CSV 파일의 데이터를 tablename 에 INSERT 하는 쿼리이다.
- 대상 테이블 : tablename
- 파일 저장 경로 : 'C:\Temp\data.csv'
- 시작 열 : 첫 행(헤더)를 건너뜀 → CSV 파일에 헤더가 있는 경우
- 열 구분자(FIELDTERMINATOR) : 쉼표(,)
- 행 구분자(ROWTERMINATOR) : 개행문자(\n)
- TABLOCK : 값 삽입 시 테이블 잠금
BULK INSERT tablename -- 대상 테이블
FROM 'C:\Temp\data.csv' -- 파일 경로
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ',', -- 열 구분자
ROWTERMINATOR = '\n', -- 행 구분자
TABLOCK -- 테이블 수준 잠금
)Example2
헤더(첫 행)를 건너뛰고 ;을 필드 종결자로, 0x0a(개행문자)를 행 종결자로 사용하여 CSV 파일을 지정하는 방법이다
BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
, FIRSTROW=2
, FIELDQUOTE = '\'
, FIELDTERMINATOR = ';'
, ROWTERMINATOR = '0x0a');
입력파일 형식 옵션
- FORMAT = 'CSV'
- 적용 대상: SQL Server 2017(14.x) CTP 1.1. RFC 4180 표준을 준수하는 쉼표로 구분된 값 파일을 지정
- FIRSTROW = first_row
- 로드할 첫 번째 행의 번호를 지정. default = 1(지정한 데이터 파일의 첫 번째 행)
- FIRSTROW는 1부터 시작
- FIELDTERMINATOR ='field_terminator'
- 필드 종결자를 지정. default = \t(탭 문자)
- 자세한 내용은 필드 및 행 종결자 지정(SQL Server)을 참조
- ROWTERMINATOR ='row_terminator'
- 행 종결자를 지정. default = \r\n(줄 바꿈 문자)
- 자세한 내용은 필드 및 행 종결자 지정(SQL Server)을 참조
- KEPPNULLS
- 대량 가져오기 작업 중 테이블 열에 대한 기본값(있는 경우)을 상속하기보다는 데이터 파일의 빈 필드에 Null 값을 유지하도록 지정
- TABLOCK
- 대량 가져오기 작업이 진행되는 동안 테이블 수준 잠금을 획득하도록 지정
- 테이블에 인덱스가 없고 TABLOCK이 지정되어 있으면 여러 클라이언트가 동시에 테이블을 로드할 수 있음
- 기본적으로 잠금 동작은 table lock on bulk load 테이블 옵션에 의해 결정
- 대량 가져오기 작업이 진행되는 동안에만 잠금을 보유하면 테이블에 대한 잠금 경합이 줄어들고 이 경우 성능이 크게 향상됨
BURK INSERT
MSSQL에서 제공하는 BURK 함수의 원문.
BULK INSERT
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] DATA_SOURCE = 'data_source_name' ]
[ [ , ] ERRORFILE = 'file_name' ]
[ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
[ [ , ] 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 ]
-- input file format options
[ [ , ] FORMAT = 'CSV' ]
[ [ , ] FIELDQUOTE = 'quote_characters']
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
)]참고
'IT > DB' 카테고리의 다른 글
| [MySQL] python mysql 1064 에러 해결방법 (0) | 2022.11.21 |
|---|
댓글