使用SqlBulkCopy时如何检索服务器生成的身份值 [英] How to retrieve server generated Identity values when using SqlBulkCopy

问题描述

我知道我可以通过不指定SqlBulkCopyOptions.KeepIdentity到我的表中使用标识列进行批量插入，如

I know I can do a bulk insert into my table with an identity column by not specifying the SqlBulkCopyOptions.KeepIdentity as mentioned here.

我想做的就是获取服务器生成的标识值，并将其放入我的数据表甚至列表中.我看到了这篇帖子，但是我希望我的代码具有通用性，并且我的所有表中都没有版本列.任何建议，不胜感激.这是我的代码:

What I would like to be able to do is get the identity values that the server generates and put them in my datatable, or even a list. I saw this post, but I want my code to be general, and I can't have a version column in all my tables. Any suggestions are much appreciated. Here is my code:

public void BulkInsert(DataTable dataTable, string DestinationTbl, int batchSize)
{
    // Get the DataTable 
    DataTable dtInsertRows = dataTable;

    using (SqlBulkCopy sbc = new SqlBulkCopy(sConnectStr))
    {
        sbc.DestinationTableName = DestinationTbl;

        // Number of records to be processed in one go
        sbc.BatchSize = batchSize;

        // Add your column mappings here
        foreach (DataColumn dCol in dtInsertRows.Columns)
        {
            sbc.ColumnMappings.Add(dCol.ColumnName, dCol.ColumnName);
        }

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
    }
}

推荐答案

AFAIK，不能.

获取身份字段值的唯一方法(我知道)是在逐行插入时使用SCOPE_IDENTITY()；或在插入整个集合时使用OUTPUT方法.

The only way (that I know of) to get the values(s) of the identity field is by using either SCOPE_IDENTITY() when you insert row-by-row; or by using the OUTPUT approach when inserting an entire set.

最简单"的方法可能是，您将对表中的记录进行SqlBulkCopy，然后稍后再取回它们.问题可能在于，可能很难正确(快速)再次从服务器获取这些行. (例如，在WHERE子句中加上IN (guid1, guid2, .., guid999998, guid999999) =会很丑陋(而且很慢))

The 'simplest' approach probably would be that you would SqlBulkCopy the records in the table and then fetch them back again later on. The problem might be that it could be hard to properly (and quickly) fetch those rows from the server again. (e.g. it would be rather ugly (and slow) to have a WHERE clause with IN (guid1, guid2, .., guid999998, guid999999) =)

我假设这里的性能是一个问题，因为您已经在使用SqlBulkCopy，所以我建议您采用OUTPUT方法，在这种情况下，您首先需要一个临时表来SqlBulkCopy您的记录.然后，表格应包含某种批次标识符(GUID?)，以允许多个胎面并排运行.您将需要一个存储过程，以将数据从登台表INSERT <table> OUTPUT inserted.* SELECT到实际目标表中，并再次清理登台表.然后，来自该过程的回退记录集将与负责填充登台表的原始数据集1:1匹配，但是当然，您不应该依赖它的顺序.换句话说:您接下来的挑战将是将返回的Identity字段与应用程序中的原始 records 匹配.

I'm assuming performance is an issue here as you're already using SqlBulkCopy so I'd suggest to go for the OUTPUT approach in which case you'll firstly need a staging table to SqlBulkCopy your records in. Said table should then be including some kind of batch-identifier (GUID?) as to allow multiple treads to run side by side. You'll need a stored procedure to INSERT <table> OUTPUT inserted.* SELECT the data from the staging-table into the actual destination table and also clean-up the staging table again. The returend recordset from said procedure would then match 1:1 to the origanal dataset responsible for filling the staging table, but off course you should NOT rely on it's order. In other words : your next challenge than will be matching the returned Identity-fields back to the original records in your application.

三思而后行，在所有情况下-逐行& SCOPY_IDENTITY()方法，速度将很慢-您需要为数据添加(或添加)键"，以将生成的ID链接回原始数据=/

Thinking things over, I'd say that in all cases -- except the row-by-row & SCOPY_IDENTITY() approach, which is going to be dog-slow -- you'll need to have (or add) a 'key' to your data to link the generated id's back to the original data =/

这篇关于使用SqlBulkCopy时如何检索服务器生成的身份值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！