SqlBulkCopy Insert with Identity Column

Question

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);
}

Repossess

Now if only there was a ay to get the @@IDENTITY back in bulk to update the source.. – Kentigera

Answer 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.

Islam

I actually need the Identity values to be generated at the time of the insert, if that helps. – Repossess

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. – Islam

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 :( – Repossess

@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. – Islam

Answer 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.

Vannavannatta

This has just solved a problem which has plagued me for several hours. – Eolian

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. – Lory

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. – Spearmint

Answer 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.

Faye

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? – Repossess

@Flying - did you turn off KeepIdentity? It needs to be FALSE for the target to assign a value. – Faye

I added the code I am using to create the SqlBulkCopy object. I dont see a field/property called KeepIdentity to set. – Repossess

@Flying - apologies, don't set it to TRUE. If it is set the destination will not auto-assign an identity – Faye

Answer 4

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.

Demmy

Answer 5

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);

Rarefy

I keep getting primary key duplicate violation. – Ergotism

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. – Rarefy

Answer 6

Yes, You are right using SqlBulkCopyOptions.KeepIdentity option then bulkcopy writer doesn't think that what is you table structure this object write from start column, so for our need, I am doing in same way to preserve identity field in my table just you have to make a extra column in you datatable object with rest of your needful columns and pass null values to this column then table automatically handles Identity.

Protozoal

Answer 7

When using the JDBC SQLServerBulkCSVFileRecord structures, the identity column DOES need to be mapped, but the value in the identity column is ignored.

Iodism

Answer 8

In my case it turned out to be blank space inside the column name and in one of the columns I had accidently used hyphon (-) instead of underscore (_) in my SQL table. I replaced blank space and hyphon with underscore in the sql table and it fixed the problem.

Astigmia

Answer 9

Cause :- There were some empty rows in the excel at the end of the data, which possibly looks like blank rows. Bulk upload was trying to upload these blank rows into the table.

Solution :- Select only the rows which contains data - copy the data into the new sheet. Say you have your data in 'Sheet 1', move it to 'Sheet 2' and delete 'Sheet 1'.

Single