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

Question

Update: Here is my solution

I have a table defined as:

CREATE TABLE [dbo].[csvrf_References]
(
    [Ident] [int] IDENTITY(1,1) NOT NULL,
    [ReferenceID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [Type] [nvarchar](255) NOT NULL,
    [Location] [nvarchar](1000) NULL,
    [Description] [nvarchar](2000) NULL,
    [CreatedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [LastUpdatedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [LastUpdatedUser] [nvarchar](100) NOT NULL DEFAULT (suser_sname()),

    CONSTRAINT [PK_References] PRIMARY KEY NONCLUSTERED ([ReferenceID] ASC)
) ON [PRIMARY]

I have a DataTable with columns that match the table column names and data types. The DataTable is filled out with DBNull.Value in CreatedOn, LastUpdatedOn and LastUpdatedUser. ReferenceID is already generated. When I call the following code I get the error below.

Code:

SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, bulkCopyTran);
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn col in table.Columns) bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
bulkCopy.WriteToServer(table);

Error:

Error trying to BulkCopy table csvrf_References
System.InvalidOperationException: Column 'CreatedOn' does not allow DBNull.Value.
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)

I have looked all over and I can't seem to find an answer for this. The SqlBulkCopy class seems not to honor default values even though it says it does. What am I doing wrong here?

Answer 1

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

Answer 2

Reading the documentation regarding SqlBulkCopy, particularly SqlBulkCopyOptions, I would draw the same conclusion that you did: SQL Server should be "smart" enough to use the default constraint where applicable, especially since you are not using the SqlBulkCopyOptions.KeepNulls attribute.

However, in this case I suspect the documentation is subtly incorrect; if not incorrect it is certainly misleading.

As you have observed, with a non-nullable field with a default constraint (in this case GetDate()) the SqlBulkCopy fails with the aforementioned error.

As a test, try creating a second table that mimics the first, but this time make the CreatedOn and LastUpdatedOn fields nullable. In my tests, using the default options (SqlBulkCopyOptions.Default) the process works without error and CreatedOn and LastUpdatedOn both have the correct DateTime value populated in the table despite the fact that the DataTable's values for those fields were DBNull.Value.

As yet another test, using the same (nullable fields) table, perform the SqlBulkCopy only this time use the SqlBulkCopyOptions.KeepNulls attribute. I suspect you will see the same results I did, that is, CreatedOn and LastUpdatedOn are both null in the table.

This behavior is similar to executing a "vanilla" T-SQL statement to insert data into the table.

Using the original table (non-nullable fields) as an example, if you execute

INSERT INTO csvrf_References ([Type], [Location], [Description], [CreatedOn], [LastUpdatedOn], [LastUpdatedUser]) 
VALUES ('test', 'test', 'test', null, null, null)

you will receive a similar error regarding null values not being allowed in the table.

However, if you omit the non-nullable fields from the statement SQL Server uses the Default Constraints for those fields:

INSERT INTO csvrf_References ([Type], [Location], [Description]
VALUES ('test', 'test', 'still testing')

Based on this, I would suggest either making the fields nullable in the table (not really a great option in my opinion) OR using a "staging" table for the SqlBulkCopy process (where the fields are nullable and have a similar default constraint in place). Once the data is in the staging table execute a second statement to move the data into the actual final destination table.

Answer 3

“SQLBulkCopy column does not allow DbNull.value” error is due to source and destination table has different column order.