asked
UPDATING OF A TABLE AFTER INSERTING A ROW INTO ANOTHER TABLE
iF I insert into a table and if i want to update another table with the primary key in the first table as the foreign key int the 2nd table?
You didn't mention the SQL server version you are using, but I assume it is SQL server 2005 or later as the previous versions have passed mainstream and extended support from Microsoft. I would recommend the output clause.[
http://msdn.microsoft.com/en-us/library/ms177564.aspx][1] I give you a short sample to get you started. DECLARE @tmp TABLE( myID INT, someOtherColum INT --something to identify the rows in second table ) INSERT INTO table1 (col1, col2) OUTPUT INSERTED.myID, col2 INTO @tmp (myID, someOtherColumn) SELECT 1 AS col1, 2 AS col12 UPDATE table2 SET myFK FROM table2 t2 INNER JOIN @tmp t ON t2.pkcolumn = t.someOtherColum [1]:
http://msdn.microsoft.com/en-us/library/ms177564.aspx
Absolutely the best method out there because it supports more than one row. Anything else is a design compromise that you'll pay for down the road.
It would be even better if you have SQL 2008 and can replace the UPDATE with a MERGE statement instead. (I have seen some terrible UPDATE statements that can give unpredictable results)
