带标识列的 SqlBulkCopy 插入

我使用 SqlBulkCopy对象将生成的几百万行插入到数据库中。唯一的问题是要插入的表有一个标识列。我已经尝试将 SqlBulkCopyOptions设置为 SqlBulkCopyOptions.KeepIdentity，并将身份列设置为 0、 DbNull.Value和 null。这些都没用。我觉得我错过了一些非常简单的东西，如果有人能启发我，那将是非常棒的。谢谢！

编辑 为了澄清，我没有在导入的 DataTable中设置标识值。我希望它们作为导入的一部分生成。

编辑2 下面是我用来创建基 SqlBulkCopy对象的代码。

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);
}
89982 次浏览
小开
最佳答案

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.

小开

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.

小开

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

小开

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.

小开

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.

小开

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

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


提交答案