Bulk insert with destination table identity column

Question

I'm trying to do a bulk insert from a DataTable into a table in SQL Server, but in my datatable I don't have an id column. When I call the WriteToServer method, it throws an exception with the message

cannot insert null in column id

Code:

CREATE TABLE [dbo].[TBL_HISTORY]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [VAR1] [int] NOT NULL,
    [VAR2] [datetime] NOT NULL,
    [VAR3] [smallint] NOT NULL
)

AND my dataTable has the following data

[VAR1], [VAR2], [VAR3]

C# code:

using(SqlBulkCopy bulk = new SqlBulkCopy(this.mConnection,SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.KeepIdentity, null))
{
    bulk.DestinationTableName = ToTable;

    bulk.WriteToServer(dt);    // here exception is thrown
}

Unknown Unknown · Accepted Answer · 2016-02-11T19:37:41

You need to remove the SqlBulkCopyOptions.KeepIdentity option. You are telling it to use the identity that you provide and there is none, aka null.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions%28v=vs.110%29.aspx

KeepIdentity

Preserve source identity values. When not specified, identity values are assigned by the destination.

Bulk insert with destination table identity column

1 Answers