c# MS SQL SqlBulkCopy 사용법- MSSQL 대용량 데이터 입력하기

수만~수십만 행의 데이터를 insert하는 C# 프로그램을 개발한다고 가정해 보죠. 

트랜잭션을 걸고 for문 돌면서 insert 쿼리 날리면 어마무시한 시간이 걸립니다. 최근 테스트한 바로는 내부 네트워크로 연결된 MS-SQL에 3만줄의 데이터를 입력하는데 3분정도가 걸렸던 것 같습니다.  너무 느립니다. 앞으로 5만줄, 10만줄이 될 수도 있는데 어쩌나 하며 구글링...

그리고 SqlBulkCopy라는 놈을 발견합니다. 이름에서 보듯 벌크로 데이터를 인서트할 수 있는가 봅니다...이놈은 MS-SQL전용이라 OleDb를 지원하지 않습니다. OleDb에서 트랜잭션을 열고 SqlBulkInsert를 OleDb 트랜잭션 하위에 둘수는 없습니다. 벌크인서트 자체가 MSSQL에만 적용가능하니 그런듯..

아무튼 사용법은 아래와 같습니다.

1. SqlCommand 를 열고 트랜잭션 생성

2. 벌크인서트 전에 다른 여러 SQL구문 처리 

3. 인서트할 테이블 구조와 동알한 DataTable 생성 및 데이터 입력

4. SqlBulkCopy.WriteToServer()함수 수행 후 close()

간단하죠? ㅎㅎ

3분걸리던 입력시간이... 3초로 줄었습니다.+_+ (네트웍환경이 안좋은 원격지라면 시간이 좀 더 걸리겠죠?)

protected DataTable dataTable = new DataTable(); dataTable.Columns.Add("col1", typeof(Int32)); dataTable.Columns.Add("col2", typeof(string)); dataTable.Columns.Add("col3", typeof(string)); string con = "connectionstring"; using (connection = new SqlConnection(con)) { tran = connection.BeginTransaction(); using (SqlCommand cmd = connection.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = "delete table where col1='1'"; cmd.ExecuteNonQuery(); } // parsing & insert using (StreamReader fileStream = new StreamReader(file_name, Encoding.UTF8)) { string line = string.Empty; while ((line = fileStream.ReadLine()) != null) { object[] data = lineData.Split(','); List<object> lstData = data.ToList(); dataTable.Rows.Add(lstData.ToArray()); } using (SqlBulkCopy bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, tran)) { bulk.BulkCopyTimeout = 0; bulk.BatchSize = dataTable.Rows.Count; bulk.DestinationTableName = "user_table"; bulk.WriteToServer(dataTable); this.dataTable.Clear(); bulk.Close(); } } tran.Commit(); }