Pass DEFAULT keyword to non-nullable column with SqlBulkCopy

Question

Is there a way to pass the DEFAULT keyword to SqlBulkCopy from an IDataReader?
Basically the equivalent of

INSERT INTO MyTable(NonNullableWithDefault)
VALUES (DEFAULT)

In my application the target tables have not-nullable columns with a default constraint on them.
Some of the data to upload has values for these columns, some does not.
For the rows where there is no value (DBNull) it should use the default constraint.

As noted in this answer the SqlBulkCopy either expects you to not pass in a ColumnMapping at all or send a value.

Alternatives I'm considering

Splitting up the data to pass in different columnmappings (complex)
Check DB for defaults and filling them in the datareader (slow)
Create seperate dump table without constraints somehow (complex, maybe slow)

I can't be the first person with this problem.
What do people use in such a scenario?

UPDATE

After much fiddling around I now fetch my entire database structure with one query and then pass that dataset to my datareaders.

SELECT
    columns.TABLE_SCHEMA                            schemaName
  , columns.TABLE_NAME                              tableName
  , columns.TABLE_SCHEMA + '.' + columns.TABLE_NAME fullTableName
  , columns.COLUMN_NAME                             columnName
  , columns.DATA_TYPE                               dataType
  , ISNULL(columns.CHARACTER_MAXIMUM_LENGTH, -1)    charlength
  , columns.COLUMN_DEFAULT                          defaultValue
  , columns.ORDINAL_POSITION                        ordinalPosition
FROM
  information_schema.columns columns

The parsing of the default value string isn't pretty and most likely not fitted for advanced cases. In our case however, it covers all bases.

private object ParseDefault(DataRow row)
{
    if(row.IsNull("defaultValue")) return null;

    var value = row.Field<string>("defaultValue");
    if (value == "(getdate())")
    {
        return DateTime.UtcNow;
    }
    var type = GetTypeForName(row.Field<string>("dataType"));
    return ParseCell(value.Trim('(', ')'), type);
}

private static object ParseCell(string value, Type info)
{
    if (value.Equals("NULL", StringComparison.OrdinalIgnoreCase))
    {
        return DBNull.Value;
    }

    int intValue;
    if (info == typeof(bool) && int.TryParse(value, out intValue))
    {
        return intValue == 1;
    }
    var foo = TypeDescriptor.GetConverter(info);
    return foo.ConvertFromInvariantString(value);
}

Advantages:

Non-nullable default values workaround
Clear validation messages (like max length)
Handle column name case matching

Disadvantages

parsing default value string
can't resolve sql side default values (like sprocs)

I came up with a similar solution but I did not need super accurate timestamps so I only evaluate the default constraint values once before copying data. Average write speed is 2000 records/second (1K/record). Not sure how that compares speed-wise but good enough for my application. — Kris Oye, Apr 29 '22 at 18:38

score 2 · Accepted Answer · answered May 29 '17 at 15:29

Is there a way to pass the DEFAULT keyword to SqlBulkCopy from an IDataReader?

Unfortunately, no. :-( If a column is NOT NULL and your bulk insert maps to it, each row must have a value for that column.

Background: At the TDS level, a bulk insert is done by sending a SQL INSERT statement written using the external tools only syntax followed by TDS structures containing column metadata then row data. None of these provide a way to say "if a NULL value is in a non-NULLable column, treat the NULL as a DEFAULT."

What do people use in such a scenario?

Hum...it's frustrating when it feels like there should be built-in, simple solution and instead all the options are painful. :-/ Without knowing more about your situation, it's hard to know which option to recommend.

One additional option: the placeholder value approach. Prior to the insert, replace the "NULLs that should be defaulted" with a placeholder value. Then, post-insert, run an update(s) to replace the placeholder(s) with database-generated default values (e.g. UPDATE ... SET Col1 = DEFAULT WHERE Col1 = *placeholder*). Your application would have to know which non-nullable columns have defaults but wouldn't have to know how to compute the defaults. I don't care for it because it uses so-called magic numbers--but it is an option.

Thanks for the informed explanation. I still hope to find a better way or I might have to resort to a lot of inserts instead :( — Boris Callens, May 30 '17 at 11:46
@BorisCallens, I'm with you--I'd really like to hear about a better way to do this, too!! If you find one and have the time, please post about it here. — Ben Gribaudo, Jun 03 '17 at 16:34
My next idea is first getting all the default constraints (see https://stackoverflow.com/questions/141682/how-do-i-find-a-default-constraint-using-information-schema), and in my IDataReader replace my NULL's with the default value myself. But I have resorted to insert+multiple values for the time being. — Boris Callens, Jun 06 '17 at 07:45

Pass DEFAULT keyword to non-nullable column with SqlBulkCopy

1 Answers1