SQL Bulk copy - insert default value

Question

I have a c#.net application in which I need to insert the default value from application to sql server by using sql bulkcopy.

Example:

SqlColumnMapping("src_col1","dest_col1");
SqlColumnMapping("src_col2","dest_col2");

in "dest_col3", I would like to insert default value.

How could I map it in app and how the default value can be inserted in database?

Thanks

score 1 · Answer 1 · answered Feb 09 '14 at 17:14

Hint: do not use SqlBulkCopy - that thing has tons of problems. Most around locking, default values also are in the game.

Use it against a temporary table ;)

THis is what I do.

Create a temp table with the proper field structure. You can make fields nullable here if they have a default value (information_schema can help you find it). THis step can be automated - 100% and it is not that hard.
SqlBulkCopy into the temp table. No locking issues.
After that you can run updates for default values ;)
INSERT INTO the final table.

Problems with SqlBulkCopy locking:

Locks the table. Exclusively.
It does not wait. It tries to get a lock, immediately. If that fails it retries. If the table is busy, it never gets the lock as it never waits until it gets one - and every new request is end of the queue.

We got hit badly by that in a ETL scenario some years back.

On top, as you found out, you can not work with default values.

I actually have that stuff totally isolated now in a separate bulk loader class and am just in the process of allowing this to UPDATE rows (by merging from the temp table).

score 1 · Answer 2 · answered Mar 15 '16 at 00:21

Here's how you do it. Create a DataTable object that has the same structure as your desitination table, except remove the columns that have a default value. If you are using DataSet Designer in Visual Studio, you can remove the columns that have default values from the TableAdapter.

Using an SqlConnection called "connection" and a DataTable object called "table", your code would look something like:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    foreach (System.Data.DataColumn c in table.Columns)
    {
        bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
    }
    bulkCopy.DestinationTableName = table.TableName;
    bulkCopy.WriteToServer(table);
}

Again, in order to use this method, you have to ensure that your DataTable object does not contain the columns that you would like to insert with default values.

SQL Bulk copy - insert default value

2 Answers2