SqlBulkCopy Insert with Identity Column

c# sql-server sqlbulkcopy identity-insert

85,367

Solution 1

To have the destination table assign the identity, DO NOT use the SqlBulkCopyOptions.KeepIdentity option. Instead, don't map the identity from the source, and don't extract it from source to send through to SqlBulkCopy.

Solution 2

Fill the ColumnMapping of the BulkCopy object and don't map the identity column. The identity column will be generated by the target database.

Solution 3

You have two options -

1 - use KeepIdentity and preserve the source's Identity values.

2 - Don't map the Identity field. If you don't try to assign a value the target table will assign one automatically.

Solution 4

This is the table

CREATE TABLE [dbo].[ProductShippingMethodMap](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [ShippingMethodId] [int] NOT NULL,
    [ParentProductId] [int] NOT NULL,
 CONSTRAINT [PK_ProductShippingMethodMap] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The bellow C# code is working

 DataTable dtQtyData = new DataTable();
        dtQtyData.Clear();
        dtQtyData.Columns.Add("Id", typeof(int));

    dtQtyData.Columns.Add("ProductId", typeof(int));
    dtQtyData.Columns.Add("ShippingMethodId", typeof(int));
    dtQtyData.Columns.Add("ParentProductId", typeof(int));


    for (int i = 0; i < ShippingMethodIds.Length; i++)
    {
        for (int j = 0; j < ProductIds.Length; j++)
        {
            var productId = ProductIds[j];
            var shippingMethodId = ShippingMethodIds[i];
            dtQtyData.Rows.Add(new object[] {0,productId, shippingMethodId, parentProductId });
        }

    }
    var connectionString = new DataSettingsManager().LoadSettings().DataConnectionString;
    SqlBulkCopy bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Default);
    bulkcopy.DestinationTableName = "ProductShippingMethodMap";
    bulkcopy.WriteToServer(dtQtyData);

Solution 5

This is how I solved it in .NET (dt is your data table):

dt.Columns.Cast<DataColumn>().ForEach((c, i) => sqlBulkCopy.ColumnMappings.Add(c.ColumnName, i + 1));

You basically skip the identity (Id) column by assigning your destination columns with an ordinal starting from 1 instead of 0.

View more solutions

85,367

Author by

FlyingStreudel

I do things sometimes

Updated on July 05, 2022

Comments

FlyingStreudel almost 2 years
I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity and setting the identity column to 0's, DbNull.Value and null. None of which have worked. I feel like I am missing something pretty simple, if someone could enlighten me that would be fantastic. Thanks!

edit To clarify, I do not have the identity values set in the DataTable I am importing. I want them to be generated as part of the import.

edit 2 Here is the code I use to create the base SqlBulkCopy object.
```
SqlBulkCopy sbc = GetBulkCopy(SqlBulkCopyOptions.KeepIdentity);
sbc.DestinationTableName = LOOKUP_TABLE;

private static SqlBulkCopy GetBulkCopy(SqlBulkCopyOptions options = 
    SqlBulkCopyOptions.Default) 
{
    Configuration cfg = WebConfigurationManager.OpenWebConfiguration("/RSWifi");
    string connString =
    cfg.ConnectionStrings.ConnectionStrings["WifiData"].ConnectionString;
    return new SqlBulkCopy(connString, options);
}
```
FlyingStreudel almost 13 years

I actually need the Identity values to be generated at the time of the insert, if that helps.
FlyingStreudel almost 13 years

Not assigning a variable is equivalent to assigning DbNull.Value. I still get the same Column 'Id' does not allow DBNull.Value. IOE :( Or did you mean don't even include the Id column in my DataTable?
JNK almost 13 years

@Flying - did you turn off KeepIdentity? It needs to be FALSE for the target to assign a value.
FlyingStreudel almost 13 years

I added the code I am using to create the SqlBulkCopy object. I dont see a field/property called KeepIdentity to set.
JNK almost 13 years

@Flying - apologies, don't set it to TRUE. If it is set the destination will not auto-assign an identity
jason almost 13 years

Oh, you want the destination to assign the value? That wasn't at all clear from your question. Don't use SqlBulkCopyOptions.KeepIdentity! Turn that off, and don't include it in the mapping nor when you pull from source.
FlyingStreudel almost 13 years

Oh, I was confused by the documentation When not specified, identity values are assigned by the destination. I thought it meant when a value wasn't specified :(
jason almost 13 years

@FlyingStreudel: Exactly. There are a bunch of options in SqlBulkCopyOptions. When you do not specify the SqlBulkCopyOptions.KeepIdentity option, the destination table will assign the identity.
Alex almost 9 years

This has just solved a problem which has plagued me for several hours.
iCode about 7 years

This helped since my source/destination had different number of columns. The source data had columns that were used for displaying data in a particular format. That also meant duplicated data in the database. So I used the ColumnMappings collection to skip certain columns in the source data that did not need to be saved in the database.
Liam almost 6 years

I didn't really understand this answer though I think it was the answer to my problem. If you don't add the identity column into the DataTable, e.g. returnVal.Columns.Add("Id", typeof(int)); then you get a mapping problem. This column shouldn't be assigned a value though, so don't add dataRow["id"] = ...etc.
Bercovici Adrian almost 4 years

I keep getting primary key duplicate violation.
sina_Islam almost 4 years

It should not be as at the table definition [Id] [int] IDENTITY(1,1) NOT NULL, is the primary key and autoincrement by 1. So it should abide by the rule.