How can I resolve this stored procedure issue?

sql sqlbulkcopy sql-server

Question

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

TableA
Id <pk> incrementing
Name varchar(50)

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

These tables are now related to one another.

Scenario

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?

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

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

close connection

Notes:

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

Alternative:

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

3
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