SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value

c#, default-constraint, null, sql-server, sqlbulkcopy

Solution

For part 1, "field that is NOT NULL with a DEFAULT", you should not be sending the field in the first place. It should not be mapped. There is no need to change that field to accept NULLs just for this.

For part 2, "field that is NULL with a DEFAULT", that will work to get the default value when passing in DbNull.Value, as long as you don't have the SqlBulkCopyOptions set to `KeepNulls`, else it will insert an actual database `NULL`.

Since there is some confusion about the SqlBulkCopyOption of `KeepNulls`, let's look at its definition:

Preserve null values in the destination table regardless of the settings for default values. When not specified, null values are replaced by default values where applicable.

This means that a DataColumn set to `DbNull.Value` will be inserted as a database `NULL`, even if the column has a DEFAULT CONSTRAINT, if the `KeepNulls` option is specified. It is not specified in your code. Which leads to the second part that says `DbNull.Value` values are replaced by "default values" where applicable. Here "applicable" means that the column has a DEFAULT CONSTRAINT defined on it. Hence, when a DEFAULT CONSTRAINT exists, a non-`DbNull.Value` value will be sent in as is while `DbNull.Value` should translate to the SQL keyword `DEFAULT`. This keyword is interpreted in an INSERT statement as taking the value of the DEFAULT constraint. Of course, it is also possible that `SqlBulkCopy`, if issuing individual INSERT statements, could simply leave that field out of the column list if set to NULL for that row, which would pick up the default value. In either case, the end result is that it works as you expected. And my testing shows that it does indeed work in this manner.

To be clear about the distinction:

If a field in the database is set to `NOT NULL` and has a DEFAULT CONSTRAINT defined on it, your options are:

Pass in the field (i.e. it will not pick up the DEFAULT value), in which case it can never be set to `DbNull.Value`

Do not pass in the field at all (i.e. it will pick up the DEFAULT value), which can be accomplished by either:

Do not have it in the DataTable or query or DataReader or whatever is being sent in as the source, in which case you might not need to specify the `ColumnMappings` collection at all

If the field is in the source, then you must specify the `ColumnMappings` collection so that you can leave that field out of the mappings.

Setting, or not setting, `KeepNulls` does not change the above noted behavior.

If a field in the database is set to `NULL` and has a DEFAULT CONSTRAINT defined on it, your options are:

Do not pass in the field at all (i.e. it will pick up the DEFAULT value), which can be accomplished by either:

Do not have it in the DataTable or query or DataReader or whatever is being sent in as the source, in which case you might not need to specify the `ColumnMappings` collection at all

If the field is in the source, then you must specify the `ColumnMappings` collection so that you can leave that field out of the mappings.

Pass in the field set to a value that is not `DbNull.Value`, in which case it will be set to this value and not pick up the DEFAULT value

Pass in the field as `DbNull.Value`, in which case the effect is determined by whether or not `SqlBulkCopyOptions` is being passed in and has been set to `KeepNulls`:

`KeepNulls` is not set will pick up the DEFAULT value

`KeepNulls` is set will leave the field set to `NULL`

Here is a simple test to see how the `DEFAULT` keyword works:

--DROP TABLE ##DefaultTest;
CREATE TABLE ##DefaultTest
(
  Col1 INT,
  [CreatedOn] [datetime] NOT NULL DEFAULT (GETDATE()),
  [LastUpdatedOn] [datetime] NULL DEFAULT (GETDATE())
);
INSERT INTO ##DefaultTest (Col1, CreatedOn) VALUES (1, DEFAULT);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (2, DEFAULT);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (3, NULL);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (4, '3333-11-22');

SELECT * FROM ##DefaultTest ORDER BY Col1 ASC;

Results:

Col1   CreatedOn                  LastUpdatedOn
1      2014-11-20 12:34:31.610    2014-11-20 12:34:31.610
2      2014-11-20 12:34:31.610    2014-11-20 12:34:31.610
3      2014-11-20 12:34:31.610    NULL
4      2014-11-20 12:34:31.613    3333-11-22 00:00:00.000

Problem

Original source