如何解决这个存储过程问题

I have 2 tables. The following are just a stripped down version of these tables.

我有两个表。下面是这些表的简化版本。

TableA
Id <pk> incrementing
Name varchar(50)

TableB
TableAId <pk> non incrementing
Name varchar(50)

Now these tables have a relationship to each other.

这些表之间有关系。

Scenario

场景

User 1 comes to my site and does some actions(in this case adds rows to Table A). So I use a SqlBulkCopy all this data in Table A.

用户1来到我的站点并执行一些操作(在本例中，向表A添加行)，因此我使用SqlBulkCopy所有这些数据在表A中。

However I need to add the data also to Table B but I don't know the newly created Id's from Table A as SQLBulkCopy won't return these.

但是，我需要将数据添加到表B中，但是我不知道新创建的Id来自表A，因为SQLBulkCopy不会返回这些数据。

So I am thinking of having a stored procedure that finds all the id's that don't exist in Table B and then insert them in.

我想要一个存储过程找到表B中不存在的所有id，然后插入它们。

INSERT INTO TableB (TableAId , Name)
SELECT Id,Name FROM TableA as tableA
WHERE not exists( ...)

However this comes with a problem. A user at any time can delete something from TableB so if a user deletes say a row and then another user comes around or even the same user comes around and does something to Table A my stored procedure will bring back that deleted row in Table B. Since it will still exist in Table A but not Table B and thus satisfy the stored procedure condition.

然而，这也带来了一个问题。用户随时可以删除从表B如果用户删除一行,然后另一个用户到来,甚至相同的用户到来,做表存储过程将恢复,删除行表B因为它仍将存在于表而不是表B,从而满足存储过程条件。

So is there a better way of dealing with two tables that need to be updated when using bulk insert?

那么，是否有更好的方法来处理使用批量插入时需要更新的两个表呢?

3 个解决方案

#1

SQLBulkCopy complicates this so I'd consider using a staging table and an OUTPUT clause

SQLBulkCopy使这一过程变得复杂，所以我考虑使用staging表和OUTPUT子句

Example, in a mixture of client pseudo code and SQL

例如，在客户机伪代码和SQL的混合中。

create SQLConnection

Create #temptable
Bulkcopy to #temptable

Call proc on same SQLConnection

proc:
   INSERT tableA (..)
   OUTPUT INSERTED.key, .. INTO TableB
   SELECT .. FROM #temptable

close connection

Notes:

注:

temptable will be local to the connection and be isolated
temptable将是本地的连接并被隔离。
the writes to A and B will be atomic
对A和B的写入是原子的
overlapping or later writes don't care about what happens later to A and B
重叠或后写不关心A和B后面会发生什么
emphasising the last point, A and B will only ever be populated from the set of rows in #temptable
强调最后一点，A和B将只从#temptable中的一组行填充

Alternative:

选择:

Add another column to A and B called sessionid and use that to identify row batches.

向A和B添加另一列sessionid，并使用它标识行批次。

#2

One option would be to use SQL Servers output clause:

一种选择是使用SQL server output子句:

INSERT YourTable (name)
OUTPUT INSERTED.*
VALUES ('NewName')

This will return the id, name of the inserted rows to the client, so you can use them in the insert operation for the second table.

这将向客户端返回已插入行的id和名称，因此可以在第二个表的insert操作中使用它们。

#3

Just as an alternative solution you could use database triggers to update the second table.

作为替代解决方案，您可以使用数据库触发器更新第二个表。

#1