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

Now if only there was a ay to get the @@IDENTITY back in bulk to update the source.. — David V. Corbin, Oct 12 '22 at 15:05

score 50 · Accepted Answer · edited Jun 25 '21 at 23:20

50

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.

edited Jun 25 '21 at 23:20

silkfire

24,585
15
82
105

answered Jul 11 '11 at 14:41

jason

236,483
35
423
525

I actually need the Identity values to be generated at the time of the insert, if that helps. – FlyingStreudel Jul 11 '11 at 14:43
4

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`. – jason Jul 11 '11 at 15:11
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 :( – FlyingStreudel Jul 11 '11 at 15:21
3

@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. – jason Jul 11 '11 at 15:45

score 33 · Answer 2 · edited Aug 13 '18 at 10:22

33

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

edited Aug 13 '18 at 10:22

Liam

27,717
28
128
190

answered Jul 11 '11 at 14:58

Wim

1,967
11
19

1

This has just solved a problem which has plagued me for several hours. – Alex Aug 17 '15 at 18:11
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. – iCode Jun 12 '17 at 11:58
4

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.` – Liam Aug 13 '18 at 10:53

score 5 · Answer 3 · answered Jul 11 '11 at 14:43

5

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.

answered Jul 11 '11 at 14:43

JNK

63,321
15
122
138

1

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`? – FlyingStreudel Jul 11 '11 at 14:47
@Flying - did you turn off `KeepIdentity`? It needs to be `FALSE` for the target to assign a value. – JNK Jul 11 '11 at 14:48
I added the code I am using to create the `SqlBulkCopy` object. I dont see a field/property called `KeepIdentity` to set. – FlyingStreudel Jul 11 '11 at 14:51
@Flying - apologies, don't set it to `TRUE`. If it is set the destination will not auto-assign an identity – JNK Jul 11 '11 at 14:53

silkfire · Answer 4 · 2021-06-25T23:19:47.803

2

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.

edited Jun 25 '21 at 23:19

answered Feb 13 '19 at 00:33

silkfire

24,585
15
82
105

score 2 · Answer 5 · answered Aug 09 '19 at 10:50

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

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. — sina_Islam, Jul 12 '20 at 18:12

score 1 · Answer 6 · edited Aug 13 '18 at 10:50

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.

score 0 · Answer 7 · answered Feb 22 '22 at 23:55

0

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

answered Feb 22 '22 at 23:55

Glen Little

6,951
4
46
68

score -1 · Answer 8 · answered Jan 20 '16 at 15:49

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.

score -2 · Answer 9 · answered Sep 03 '14 at 09:46

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

SqlBulkCopy Insert with Identity Column

9 Answers9

Linked

Related