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

问题描述

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

这2个具有PK/FK关系.

 表AField1 -PK自动递增(直接执行SqlBulkCopy很容易)表BField1 -PK/FK-此字段建立关系，并且也是此表的PK.它不是自动递增的，并且需要具有与表A中的行相同的ID. 

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

修改

我可以做这样的事情吗?

  SELECT *从产品不存在的地方(从ProductReview中选择* *，其中Product.ProductId = ProductReview.ProductId AND Product.Qty = NULL AND Product.ProductName！='Ipad') 

这应该找到刚与sql批量复制一起插入的所有行.我不确定如何从中获取结果，然后从SP中进行批量插入.

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

所以说我像一个用户使用手动方式，而另一个用户几乎同时进行批量方式.

手动方式.1.用户提交数据2.生成Linq to sql产品对象并填充数据并提交.3.该对象现在包含ProductId4.为产品查看表创建另一个linq to sql对象，并将其插入(将发送第3步中的产品ID).

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

因此，如果第3步(手动方式)与第4步(大量方式)同时发生，会发生什么.我认为它将尝试在同一行中插入两次，从而导致执行主约束.

解决方案

在那种情况下，我将使用 SqlBulkCopy 插入到 staging 表中(即看起来像这样的表)就像我要导入的数据一样，但不属于主要事务表)，然后在数据库中移至 INSERT / SELECT 以将数据移至第一个真实表.

现在，根据服务器版本，我有两种选择:我可以对第二个实际表进行第二次 INSERT / SELECT ，或者可以使用 INSERT / OUTPUT 子句使用表中的标识行进行第二次插入.

例如:

 -虚拟架构创建表TMP(数据varchar(max))创建表[Table1](id int不为空，identity(1,1)，数据varchar(max))创建表[Table2](id int不为空，identity(1,1)，id1 int不为空，data varchar(max))-想象这是SqlBulkCopy插入TMP值('abc')插入TMP值('def')插入TMP值('ghi')-现在推入真实表插入[表1]OUTPUT INSERTED.id，INSERTED.data进入[Table2](id1，data)从TMP选择数据 

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.

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.

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.

Edit

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

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.

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.

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

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.

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.

解决方案

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.

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.

For example:

     -- 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之后可以找回PrimaryKey ID吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！