트랜잭션 및 대량 복사 작업
대량 복사 작업은 격리된 작업이나 여러 단계 트랜잭션의 일부로 수행할 수 있습니다. 후자 옵션을 사용하면 동일한 트랜잭션 내에서 둘 이상의 대량 복사 작업을 수행할 뿐만 아니라 전체 트랜잭션을 커밋하거나 롤백하면서 삽입, 업데이트 및 삭제 등 다른 데이터베이스 작업을 수행할 수 있습니다.
기본적으로 대량 복사 작업은 격리된 작업으로 수행됩니다. 대량 복사 작업은 롤백할 수 없는 비트랜잭트 방식으로 이루어집니다. 오류가 발생할 때 대량 복사의 전체 또는 일부를 롤백해야 하는 경우 다음을 작업을 수행할 수 있습니다.
SqlBulkCopy 관리 트랜잭션을 사용
기존 트랜잭션 내에서 대량 복사 작업 수행
System.TransactionsTransaction에 등록.
비트랜잭트 대량 복사 작업 수행
다음 콘솔 애플리케이션은 비트랜잭트 대량 복사 작업에서 작업 중에 오류가 발생하는 경우를 보여 줍니다.
이 예제에서 소스 테이블과 대상 테이블에는 ProductID라는 Identity 열이 각각 포함되어 있습니다. 먼저 코드는 모든 행을 삭제한 다음 ProductID가 원본 테이블에 있는 것으로 알려진 단일 행을 삽입하여 대상 테이블을 준비합니다. 기본적으로 Identity 열의 새 값이 대상 테이블의 추가된 행마다 생성됩니다. 이 예제에서 옵션은 대량 로드 프로세스에서 원본 테이블의 Identity 값을 대신 강제로 사용하도록 하는 연결을 열 때 설정됩니다.
대량 복사 작업은 BatchSize 속성을 10으로 설정하여 실행합니다. 작업에서 잘못된 행이 발생하는 경우 예외가 throw됩니다. 이 첫 번째 예제에서는 대량 복사 작업이 트랜잭션되지 않습니다. 오류 지점까지 복사된 모든 일괄 처리가 커밋됩니다. 중복 키가 포함된 배치는 롤백되고 대량 복사 작업은 다시 기본 배치를 처리하기 전에 중단됩니다.
참고 항목
이 샘플은 가져올 대량 복사 샘플 설정에 설명된 대로 작업 테이블을 만들지 않은 경우 실행되지 않습니다. 이 코드는 SqlBulkCopy를 사용하는 구문을 보여 주는 용도로 제공됩니다. 소스 테이블과 대상 테이블이 동일한 SQL Server 인스턴스에 있으면 Transact-SQL INSERT … SELECT 문을 사용하여 데이터를 더 쉽고 빠르게 복사할 수 있습니다.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new SqlCommand();
commandDelete.Connection = sourceConnection;
commandDelete.CommandText =
"DELETE FROM dbo.BulkCopyDemoMatchingColumns";
commandDelete.ExecuteNonQuery();
// Add a single row that will result in duplicate key
// when all rows from source are bulk copied.
// Note that this technique will only be successful in
// illustrating the point if a row with ProductID = 446
// exists in the AdventureWorks Production.Products table.
// If you have made changes to the data in this table, change
// the SQL statement in the code to add a ProductID that
// does exist in your version of the Production.Products
// table. Choose any ProductID in the middle of the table
// (not first or last row) to best illustrate the result.
SqlCommand commandInsert = new SqlCommand();
commandInsert.Connection = sourceConnection;
commandInsert.CommandText =
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
"INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
"([ProductID], [Name] ,[ProductNumber]) " +
"VALUES(446, 'Lock Nut 23','LN-3416');" +
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
commandInsert.ExecuteNonQuery();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
// Set up the bulk copy object using the KeepIdentity option.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
connectionString, SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error
// after some of the batches have been copied.
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
// Print the number of rows processed using the
// RowsCopied property.
Console.WriteLine("{0} rows were processed.",
bulkCopy.RowsCopied);
Console.WriteLine(ex.Message);
}
finally
{
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
// Note that for this scenario, the value will
// not be equal to the RowsCopied property.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
트랜잭션에서 전용 대량 복사 작업 수행
기본적으로 대량 복사 작업은 그 자체만으로도 고유한 트랜잭션입니다. 전용 대량 복사 작업을 수행하려면 연결 문자열을 사용하여 SqlBulkCopy의 인스턴스를 만들거나 활성 트랜잭션 없이 기존 SqlConnection 개체를 사용합니다. 각 시나리오에서 대량 복사 작업은 트랜잭션을 만든 다음 커밋 또는 롤백합니다.
UseInternalTransaction 클래스 생성자의 SqlBulkCopy 옵션을 명시적으로 지정 하여 자체 트랜잭션에서 대량 복사 작업을 실행할 수 있습니다. 작업의 각 배치는 별도의 트랜잭션 내에서 실행됩니다.
참고 항목
배치가 실행되는 트랜잭션이 각각 다르기 때문에 대량 복사 작업 중에 오류가 발생할 경우 현재 배치 내의 모든 행이 롤백되지만 이전 배치의 행은 데이터베이스에 유지됩니다.
다음 콘솔 애플리케이션은 다음 한 가지를 제외하면 앞의 예제와 유사합니다. 이 예제에서 대량 복사 작업은 고유 트랜잭션을 관리합니다. 오류 지점까지 복사된 모든 일괄 처리가 커밋됩니다. 중복 키가 포함된 배치는 롤백되고 대량 복사 작업은 다시 기본 배치를 처리하기 전에 중단됩니다.
Important
이 샘플은 가져올 대량 복사 샘플 설정에 설명된 대로 작업 테이블을 만들지 않은 경우 실행되지 않습니다. 이 코드는 SqlBulkCopy를 사용하는 구문을 보여 주는 용도로 제공됩니다. 소스 테이블과 대상 테이블이 동일한 SQL Server 인스턴스에 있으면 Transact-SQL INSERT … SELECT 문을 사용하여 데이터를 더 쉽고 빠르게 복사할 수 있습니다.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new SqlCommand();
commandDelete.Connection = sourceConnection;
commandDelete.CommandText =
"DELETE FROM dbo.BulkCopyDemoMatchingColumns";
commandDelete.ExecuteNonQuery();
// Add a single row that will result in duplicate key
// when all rows from source are bulk copied.
// Note that this technique will only be successful in
// illustrating the point if a row with ProductID = 446
// exists in the AdventureWorks Production.Products table.
// If you have made changes to the data in this table, change
// the SQL statement in the code to add a ProductID that
// does exist in your version of the Production.Products
// table. Choose any ProductID in the middle of the table
// (not first or last row) to best illustrate the result.
SqlCommand commandInsert = new SqlCommand();
commandInsert.Connection = sourceConnection;
commandInsert.CommandText =
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
"INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
"([ProductID], [Name] ,[ProductNumber]) " +
"VALUES(446, 'Lock Nut 23','LN-3416');" +
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
commandInsert.ExecuteNonQuery();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
// Set up the bulk copy object.
// Note that when specifying the UseInternalTransaction
// option, you cannot also specify an external transaction.
// Therefore, you must use the SqlBulkCopy construct that
// requires a string for the connection, rather than an
// existing SqlConnection object.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
connectionString, SqlBulkCopyOptions.KeepIdentity |
SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error
// after some of the batches have been copied.
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
// Print the number of rows processed using the
// RowsCopied property.
Console.WriteLine("{0} rows were processed.",
bulkCopy.RowsCopied);
Console.WriteLine(ex.Message);
}
finally
{
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
// Note that for this scenario, the value will
// not be equal to the RowsCopied property.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
기존 트랜잭션 사용
SqlBulkCopy 생성자에서 기존 SqlTransaction 개체를 매개 변수로 지정할 수 있습니다. 이 상황에서는 대량 복사 작업이 기존 트랜잭션에서 수행되며 트랜잭션 상태가 변경되지 않습니다. 즉, 커밋되거나 중단되지 않습니다. 이 메서드로 애플리케이션에서 다른 데이터베이스 작업과 함께 대량 복사 작업을 하나의 트랜잭션에 포함시킬 수 있습니다. 그러나 SqlTransaction 개체를 지정하지 않고 null 참조를 전달하며 연결에 활성 트랜잭션이 있는 경우에는 예외가 throw됩니다.
오류가 발생하여 전체 대량 복사 작업을 롤백해야 하는 경우나 롤백할 수 있는 보다 큰 프로세스의 일부로 대량 복사를 실행해야 하는 경우에는 SqlTransaction 개체를 SqlBulkCopy 생성자에 제공할 수 있습니다.
다음 콘솔 애플리케이션은 다음 한 가지를 제외하면 첫 번째(비트랜잭트 방식) 예제와 유사합니다. 이 예제에서는 대량 복사 작업이 보다 큰 외부 트랜잭션에 포함됩니다. 기본 키 위반 오류가 발생하는 경우에는 전체 트랜잭션이 롤백되고 대상 테이블에 행이 추가되지 않습니다.
Important
이 샘플은 가져올 대량 복사 샘플 설정에 설명된 대로 작업 테이블을 만들지 않은 경우 실행되지 않습니다. 이 코드는 SqlBulkCopy를 사용하는 구문을 보여 주는 용도로 제공됩니다. 소스 테이블과 대상 테이블이 동일한 SQL Server 인스턴스에 있으면 Transact-SQL INSERT … SELECT 문을 사용하여 데이터를 더 쉽고 빠르게 복사할 수 있습니다.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new SqlCommand();
commandDelete.Connection = sourceConnection;
commandDelete.CommandText =
"DELETE FROM dbo.BulkCopyDemoMatchingColumns";
commandDelete.ExecuteNonQuery();
// Add a single row that will result in duplicate key
// when all rows from source are bulk copied.
// Note that this technique will only be successful in
// illustrating the point if a row with ProductID = 446
// exists in the AdventureWorks Production.Products table.
// If you have made changes to the data in this table, change
// the SQL statement in the code to add a ProductID that
// does exist in your version of the Production.Products
// table. Choose any ProductID in the middle of the table
// (not first or last row) to best illustrate the result.
SqlCommand commandInsert = new SqlCommand();
commandInsert.Connection = sourceConnection;
commandInsert.CommandText =
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
"INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
"([ProductID], [Name] ,[ProductNumber]) " +
"VALUES(446, 'Lock Nut 23','LN-3416');" +
"SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
commandInsert.ExecuteNonQuery();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
//Set up the bulk copy object inside the transaction.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction =
destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error.
try
{
bulkCopy.WriteToServer(reader);
transaction.Commit();
}
catch (Exception ex)
{
// Print the number of rows processed using the
// RowsCopied property.
Console.WriteLine("{0} rows were processed.",
bulkCopy.RowsCopied);
Console.WriteLine(ex.Message);
transaction.Rollback();
}
finally
{
reader.Close();
}
}
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
다음 단계
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기