SQL BulkCopy 后是否可以获取主键 ID？

Question

SQL BulkCopy 后是否可以获取主键 ID？

c#sqlsql-server-2005sqlbulkcopy

27

我正在使用C#和SqlBulkCopy进行操作。但是我遇到了一个问题。我需要在一张表中进行批量插入，然后在另一张表中进行另一批量插入。

这两个表之间存在主键/外键关系。

Table A
Field1 -PK auto incrementing (easy to do SqlBulkCopy as straight forward)

Table B
Field1 -PK/FK - This field makes the relationship and is also the PK of this table. It is not auto incrementing and needs to have the same id as to the row in Table A.

所以这些表有一个一对一的关系，但我不确定如何获取批量插入中生成的所有主键ID，因为我需要它们用于表B。

编辑

我可以像这样做吗？

SELECT * 
FROM Product
WHERE NOT EXISTS (SELECT * FROM ProductReview WHERE Product.ProductId = ProductReview.ProductId AND Product.Qty = NULL AND Product.ProductName != 'Ipad')

这应该可以找到所有刚刚使用 SQL 批量复制插入的行。我不确定如何从中获取结果，然后通过存储过程批量插入它们。唯一的问题是，如果一个用户一次只插入一条记录，并且此时另一个用户运行此语句，它可能会尝试将一行两次插入“产品评论表”中。因此，假设有一个用户手动进行记录，而另一个用户同时使用批量方式。手动方式：1. 用户提交数据 2. 创建 Linq to sql Product 对象并填充数据并提交。3. 此对象现在包含 ProductId。4. 为 "Product Review Table" 创建另一个 linq to sql 对象并插入(Product Id 从步骤 3 发送) 。批量方式：1. 用户从共享数据的用户那里获取数据。2. 获取来自共享用户的所有产品行。3. 在产品行上发生 SQL 批量复制插入。4. 我的存储过程选择仅存在于 Product 表格中且符合其他条件的所有行。5. 使用这些行进行批量插入。如果步骤 3 (手动方式) 与步骤 4 (批量方式) 同时发生会发生什么？我认为它会尝试插入相同的行两次，导致主键约束异常。

- chobo2

预先绑定表格的是什么？ - Ralf de Kleine

7个回答

10

如果您的应用程序允许，您可以添加另一列来存储批量插入的标识符（例如GUID）。您需要显式设置此ID。然后，在批量插入之后，只需选择具有该标识符的行即可。

- Cosmin

6

我遇到了同样的问题，需要获取使用SqlBulkCopy插入的行的ID。我的ID列是自增列。

解决方案：

我使用批量复制插入了500多行数据，然后使用以下查询语句将它们选回来：

SELECT TOP InsertedRowCount * 
FROM   MyTable 
ORDER BY ID DESC

这个查询返回我刚插入的行以及它们的ID。在我的情况下，我还有另一个唯一列。所以我选择了该列和ID，并将它们映射到一个 IDictionary 中，如下所示：

 IDictionary<string, int> mymap = new Dictionary<string, int>()
 mymap[Name] = ID

希望这能帮助到您。

- Vitaly Galinsky

20

这是一个好的解决方案，但仅当您能够保证在您插入但在选择项目之前没有来自另一个线程的记录被插入时。 - marknuzz

除了@nuzzolilo提出的有效观点之外，这个答案值得更多的赞同。 - Dirk Boer

3

我的方法与RiceRiceBaby描述的类似，但需要补充一点重要的内容，即检索Max(Id)的调用必须是事务的一部分，还要包括对SqlBulkCopy.WriteToServer的调用。否则，在您的事务期间，其他人可能会进行插入操作，这将使您的Id不正确。以下是我的代码：

public static void BulkInsert<T>(List<ColumnInfo> columnInfo, List<T> data, string 
destinationTableName, SqlConnection conn = null, string idColumn = "Id")
    {
        NLogger logger = new NLogger();

        var closeConn = false;


        if (conn == null)
        {
            closeConn = true;
            conn = new SqlConnection(_connectionString);
            conn.Open();
        }

        SqlTransaction tran = 
    conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

        try
        {
            var options = SqlBulkCopyOptions.KeepIdentity;
            var sbc = new SqlBulkCopy(conn, options, tran);

            var command = new SqlCommand(
                    $"SELECT Max({idColumn}) from {destinationTableName};", conn, 
           tran);
            var id = command.ExecuteScalar();

            int maxId = 0;

            if (id != null && id != DBNull.Value)
            {
                maxId = Convert.ToInt32(id);
            }

            data.ForEach(d =>
                {
                    maxId++;
                    d.GetType().GetProperty(idColumn).SetValue(d, maxId);
                });

            var dt = ConvertToDataTable(columnInfo, data);

            sbc.DestinationTableName = destinationTableName;

            foreach (System.Data.DataColumn dc in dt.Columns)
            {
                sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
            }

            sbc.WriteToServer(dt);

            tran.Commit();

            if(closeConn)
            {
                conn.Close();
                conn = null;
            }
        }
        catch (Exception ex)
        {
            tran.Rollback();
            logger.Write(LogLevel.Error, $@"An error occurred while performing a bulk 
insert into table {destinationTableName}. The entire
                                           transaction has been rolled back. 

{ex.ToString()}");
            throw ex;
        }
    }

- Becca

我考虑过像你的方法，但我不认为这解决了ID冲突的问题。使用“事务”并不能阻止其他人使用该表，插入行并在你完成插入之前消耗ID。在完成插入之前进行任何单个插入，都会导致失败。此外，“sbc”必须被处理掉。 - DonBoitnott

我错过了什么？根据微软文档，如果使用 System.Data.IsolationLevel.Serializable，则“在数据集上放置范围锁，防止其他用户在事务完成之前更新或插入行。”我还通过尝试在进行大容量插入时从 SSMS 插入来进行了测试，并且我的插入始终发生在整个大容量插入完成后。 - Becca

显然，我对可序列化的理解是不完整的。现在我仔细研究了它，我明白你的意思了。看起来应该阻止后续的写入，直到批量插入完成。我的错误。 - DonBoitnott

没问题！它相当耐用，我已经在生产中使用一段时间了，没有任何问题。 - Becca

0

根据您的需求和对表格的控制程度，您可能需要考虑使用UNIQUEIDENTIFIERs（Guids）代替IDENTITY主键。这将把关键管理移出数据库并移到应用程序中。这种方法存在一些严重的权衡，因此可能无法满足您的需求。但是考虑一下也许是值得的。如果您确定将通过批量插入大量数据到表中，那么在对象模型中管理这些键通常非常方便，而不是依赖于数据库返回数据。

您还可以采用之前建议的分阶段表格的混合方法。使用GUIDs建立关系将数据放入这些表格中，然后通过SQL语句按顺序获取整数外键并将数据注入生产表格中。

- mattmc3

0

我会：

在表上启用标识插入
获取表中最后一行的 Id
从 (int i = Id; i < datable.rows.count+1; i++) 循环
在循环中，将您的数据表的 Id 属性分配给 i+1。
使用保留标识符运行 SQL 批量插入。
关闭标识插入

我认为这是在 SQL 批量插入中获得 ID 的最安全方法，因为它可以防止由应用程序在另一个线程上执行而导致的不匹配的 ID。

- RiceRiceBaby

-2

免责声明：我是项目C#批量操作的所有者。

该库克服了SqlBulkCopy的限制，并添加了灵活的功能，如输出插入的标识值。

在代码背后，它与被接受的答案完全相同，但使用起来更加容易。

var bulk = new BulkOperation(connection);

// Output Identity
bulk.ColumnMappings.Add("ProductID", ColumnMappingDirectionType.Output);
// ... Column Mappings...

bulk.BulkInsert(dt);

- Jonathan Magnan

12

我在想，人们如何在这个论坛上推销他们的产品。:-)与SklBulkCopy相关的功能只需花费这么少的代价就可以完成封装... 不可思议。 - Usman

你好，能否详细说明如何获取插入的行ID、日期和时间？我尝试了你的代码，但似乎无法获取成功插入的行ID。谢谢。 - Transformer

这里有一个演示如何返回值的 Fiddle：https://dotnetfiddle.net/g5pSS1，如果要返回更多列，只需将列与方向“Output”映射即可。 - Jonathan Magnan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marc Gravell · Accepted Answer

在这种情况下，我将使用SqlBulkCopy来插入到一个暂存表中（即看起来像我想要导入的数据，但不是主事务表的一部分），然后在数据库中使用INSERT/SELECT将数据移动到第一个真实表中。

现在我有两个选择，具体取决于服务器版本：我可以使用第二个INSERT/SELECT将数据插入到第二个真实表中，或者我可以使用INSERT/OUTPUT子句进行第二次插入，利用表中的标识行。

例如:

     -- dummy schema
     CREATE TABLE TMP (data varchar(max))
     CREATE TABLE [Table1] (id int not null identity(1,1), data varchar(max))
     CREATE TABLE [Table2] (id int not null identity(1,1), id1 int not null, data varchar(max))

     -- imagine this is the SqlBulkCopy
     INSERT TMP VALUES('abc')
     INSERT TMP VALUES('def')
     INSERT TMP VALUES('ghi')

     -- now push into the real tables
     INSERT [Table1]
     OUTPUT INSERTED.id, INSERTED.data INTO [Table2](id1,data)
     SELECT data FROM TMP