可以在 SQL BulkCopy 后取回主键 ID 吗? [英] Possible to get PrimaryKey IDs back after a SQL BulkCopy?

问题描述

我正在使用 C# 和 SqlBulkCopy.我有一个问题.我需要在一个表中进行一次批量插入，然后在另一张表中进行一次批量插入.

I am using C# and using SqlBulkCopy. I have a problem though. I need to do a mass insert into one table then another mass insert into another table.

这两个有 PK/FK 关系.

These 2 have a PK/FK relationship.

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.

所以这些表具有一对一的关系，但我不确定如何取回大量插入的所有 PK Id，因为我需要它们用于表 B.

So these tables have a one to one relationship but I am unsure how to get back all those PK Id that the mass insert made since I need them for Table B.

编辑

我可以做这样的事情吗?

Could I do something like this?

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

这应该会找到刚刚插入 sql 批量复制的所有行.我不知道如何从中获取结果，然后从 SP 中对它们进行大量插入.

This should find all the rows that where just inserted with the sql bulk copy. I am not sure how to take the results from this then do a mass insert with them from a SP.

我能看到的唯一问题是，如果用户一次做一个记录并且同时运行一条 this 语句，它可能会尝试在产品审查表"中插入一行两次.

The only problem I can see with this is that if a user is doing the records one at a time and a this statement runs at the same time it could try to insert a row twice into the "Product Review Table".

假设我有一个用户使用手动方式，另一个用户同时使用批量方式.

So say I got like one user using the manual way and another user doing the mass way at about the same time.

手动方式.1. 用户提交数据2. Linq to sql Product 对象被制作并填充数据并提交.3. 该对象现在包含 ProductId4. 为产品评论表创建另一个 linq to sql 对象并插入(发送第 3 步中的产品 ID).

manual way. 1. User submits data 2. Linq to sql Product object is made and filled with the data and submited. 3. this object now contains the ProductId 4. Another linq to sql object is made for the Product review table and is inserted(Product Id from step 3 is sent along).

大众方式.1. 用户从共享数据的用户那里获取数据.2. 抓取共享用户的所有产品行.3. SQL 批量复制插入 Product 行.4.我的SP选择所有只存在于Product表中并且满足一些其他条件的行5. 大量插入发生在这些行中.

Mass way. 1. User grabs data from a user sharing the data. 2. All Product rows from the sharing user are grabbed. 3. SQL Bulk copy insert on Product rows happens. 4. My SP selects all rows that only exist in the Product table and meets some other conditions 5. Mass insert happens with those rows.

如果第 3 步(手动方式)与第 4 步(批量方式)同时发生会发生什么.我认为它会尝试两次插入同一行导致主要约束执行.

So what happens if step 3(manual way) is happening at the same time as step 4(mass way). I think it would try to insert the same row twice causing a primary constraint execption.

推荐答案

在那种情况下，我会使用 SqlBulkCopy 插入到 staging 表(即看起来像我想导入的数据，但不是主事务表的一部分)，然后在数据库中使用 INSERT/SELECT 将数据移动到第一个真正的表.

In that scenario, I would use SqlBulkCopy to insert into a staging table (i.e. one that looks like the data I want to import, but isn't part of the main transactional tables), and then at the DB to a INSERT/SELECT to move the data into the first real table.

现在我有两种选择，具体取决于服务器版本；我可以对第二个实表执行第二个 INSERT/SELECT，或者我可以使用 INSERT/OUTPUT子句使用表中的标识行进行第二次插入.

Now I have two choices depending on the server version; I could do a second INSERT/SELECT to the second real table, or I could use the INSERT/OUTPUT clause to do the second insert , using the identity rows from the table.

例如:

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

这篇关于可以在 SQL BulkCopy 后取回主键 ID 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！