SqlBulkCopy Insert with Identity Column
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
.
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.
Fill the ColumnMapping
of the BulkCopy
object and don't map the identity column. The identity column will be generated by the target database.
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);