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.
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?
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
To distinguish row batches, add a column named sessionid to A and B.