[C#/COMMON] SqlBulkCopy 클래스 : 대용량 데이터 추가하기

■ SqlBulkCopy 클래스를 사용해 대용량 데이터를 추가하는 방법을 보여준다.

▶ 예제 코드 (C#)


using System.Data;
using System.Data.SqlClient;

#region 대용량 데이터 추가하기 - BulkInsert(connectionString, targetTableName, sourceTable, batchSize)

/// <summary>
/// 대용량 데이터 추가하기
/// </summary>
/// <param name="connectionString">연결 문자열</param>
/// <param name="targetTableName">타겟 테이블명</param>
/// <param name="sourceTable">소스 테이블</param>
/// <param name="batchSize">배치 크기</param>
public void BulkInsert(string connectionString, string targetTableName, DataTable sourceTable, int batchSize)
{
    using(SqlConnection sqlConnection = new SqlConnection(connectionString))
    {
        sqlConnection.Open();

        using(SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
        {
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy
            (
                sqlConnection,
                SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepNulls,
                sqlTransaction
            );

            sqlBulkCopy.DestinationTableName = targetTableName;
            sqlBulkCopy.BatchSize            = batchSize;

            sqlBulkCopy.WriteToServer(sourceTable);

            sqlTransaction.Commit();
        }

        sqlConnection.Close();
    }
}

#endregion

using System.Data;

using System.Data.SqlClient;

#region 대용량 데이터 추가하기 - BulkInsert(connectionString, targetTableName, sourceTable, batchSize)

/// <summary>

/// 대용량 데이터 추가하기

/// </summary>

/// <param name="connectionString">연결 문자열</param>

/// <param name="targetTableName">타겟 테이블명</param>

/// <param name="sourceTable">소스 테이블</param>

/// <param name="batchSize">배치 크기</param>

public void BulkInsert(string connectionString, string targetTableName, DataTable sourceTable, int batchSize)

{

using(SqlConnection sqlConnection = new SqlConnection(connectionString))

{

sqlConnection.Open();

using(SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())

{

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy

(

sqlConnection,

SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepNulls,

sqlTransaction

);

sqlBulkCopy.DestinationTableName = targetTableName;

sqlBulkCopy.BatchSize = batchSize;

sqlBulkCopy.WriteToServer(sourceTable);

sqlTransaction.Commit();

}

sqlConnection.Close();

}

#endregion