How can I resolve this stored procedure issue?

sql sqlbulkcopy sql-server


I own two tables. These tables are only slightly modified in the following.

Id <pk> incrementing
Name varchar(50)

TableAId <pk> non incrementing
Name varchar(50)

These tables are now related to one another.


When user 1 visits my website, they do various activities (in this case adds rows to Table A). To copy all of this data into Table A, I utilize a SqlBulkCopy.

But since SQLBulkCopy won't return freshly generated Ids from Table A, I don't know how to add the data to Table B.

Therefore, I'm considering creating a stored procedure that locates all the IDs that are missing from Table B and then adds them there.

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

However, there is a drawback to this. Any user has the ability to remove data from TableB at any time, so if one person, for example, deletes a row and then another user, or even the same user, comes along and modifies Table A, my stored procedure will restore the deleted row in Table B. Since it will still be present in Table A but not Table B, the stored process requirement will be satisfied.

Is there a better method to do bulk insert when there are two tables that need to be updated?

8/12/2010 7:14:11 PM

Accepted Answer

This is complicated by SQLBulkCopy, so I'd think about using a staging table and a clause OUTPUT

As an example, using SQL mixed with client pseudo code

create SQLConnection

Create #temptable
Bulkcopy to #temptable

Call proc on same SQLConnection

   INSERT tableA (..)
   SELECT .. FROM #temptable

close connection


  • temptable will be isolated and local to the connection.

  • There will be atomic writes to both A and B.
  • What happens to A and B afterwards is irrelevant to overlapping or later writes.
  • To emphasize the latter point, only the rows in #temptable will ever be used to fill A and B.


To distinguish row batches, add a column named sessionid to A and B.

8/12/2010 7:41:49 PM

Popular Answer

Simply as an option, you might update the second table with repository triggers.

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow