CODEWITHSUNDEEP
c#sqlsql-serversqlbulkcopy
Jeff
Jeff

Reputation: 449

Does SQLBulkCopy Upload any data upon failure?

I am doing a sql bulk copy of data from an excel spreadsheet to a temp table, and then from a temp table to a production database.

My question is this, if for any reason the upload fails either to the temp table or to the production database, does the transaction get rolled back and no data gets imported or modified existing data?

Upvotes: 3

Views: 1906

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127593

By default SqlBulkCopy will roll back to the last batch you completed. If you have a BatchSize of 0 (the default value) it will do it all in a single batch but you may get a timeout if the batch takes too long to upload (default 30 seconds).

Another option is wrap the entire thing in a external transaction and pass it in to the constructor. This will roll back the entire insert operation on a error instead of just the last batch, this allows you to use smaller batches but still have the entire insert be a single transaction. This also lets you use that same transaction for moving data from the temporary staging table in to your live data.

Here is a snippit taken from the MSDN

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)
            {
                Console.WriteLine(ex.Message);
                transaction.Rollback();
            }
            finally
            {
                reader.Close();
            }
        }
    }
}

Upvotes: 3

Related Questions