Questions About SQl BulkCopy

Question

Hi

I am wondering how can do a mass insert and bulk copy at the same time? I have 2 tables that should be affect by the bulk copy as they both depend on each other.

So I want it that if while inserting table 1 a record dies it gets rolled back and table 2 never gets updated. Also if table 1 inserts good and table 2 an update fails table 1 gets rolled back.

Can this be done with bulk copy?

Edit

I should have mentioned I am doing the bulk insert though C#.

It sort of looks like this but this is an example I been working off. So I am not sure if I have to alter it to be a stored procedure(not sure how it would look and how the C# code would look)

private static void BatchBulkCopy()
{
    // Get the DataTable 
    DataTable dtInsertRows = GetDataTable();

    using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
    {
        sbc.DestinationTableName = "TBL_TEST_TEST";

        // Number of records to be processed in one go
        sbc.BatchSize = 500000;

        // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
        // sbc.ColumnMappings.Add("ID", "ID");
        sbc.ColumnMappings.Add("NAME", "NAME");

        // Number of records after which client has to be notified about its status
        sbc.NotifyAfter = dtInsertRows.Rows.Count;

        // Event that gets fired when NotifyAfter number of records are processed.
        sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
        sbc.Close();
    }

}

Answer 1

You can run bulk inserts inside of a user defined transaction so do something like this:

BEGIN TRANSACTION MyDataLoad
BEGIN TRY

BULK INSERT ...

BULK INSERT ...

COMMIT TRANSACTJION MyDataLoad
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

However, there may be other ways to accomplish what you want. Are the tables empty before you bulk insert into them? When you say the tables depend on each other, do you mean that there are foreign key constraints you want enforced?

Answer 2

I am wondering how can do a mass insert and bulk copy at the same time? I have 2 tables that should be affect by the bulk copy as they both depend on each other. So I want it that if while inserting table 1 a record dies it gets rolled back and table 2 never gets updated. Also if table 1 inserts good and table 2 an update fails table 1 gets rolled back. Can this be done with bulk copy?

No - the whole point of SqlBulkCopy is to get data into your database as fast as possible. It will just dump the data into a single table.

The normal use case will be to then inspect that table once it's imported, and begin to "split up" that data and store it into whatever place it needs to go - typically through a stored procedure (since the data already is on the server, and you want to distribute it to other tables - you don't want to pull all that data back down to the client, inspect it, and then send it back to the server one more time).

SqlBulkCopy only grabs a bunch of data and drops it into a table - very quickly so. It cannot split up data into multiple tables based on criteria or conditions.