How to fix this stored procedure problem.

Question

Hi

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.

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.

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.

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.

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

Answer 1

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

Example, in a mixture of client pseudo code and 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
• the writes to A and B will be atomic
• overlapping or later writes don't care about what happens later to A and B
• emphasising the last point, A and B will only ever be populated from the set of rows in #temptable

Alternative:

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

Answer 2

One option would be to use SQL Servers output clause:

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.

Answer 3

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

Link in comments is broken. Fire triggers http://bit.ly/amIjwI