Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steppinthrax
Starting Member
27 Posts |
 Posted - 2009-09-29 : 12:18:26
|
| Essentially this is what I'm doing.I have a 3 table database. One of the tables is the main table having a primary key with identity seed of (1). I need to insert data into this table while at the same time inserting related data in the other two tables. In order for the data to be related or corespond with the main table data, I will need to take the identity from the main table and use it in the other tables.In other words, how do I use the identity seed that is generated upon insert to be used for the insertion of data into other tables. AND the insert either needs to FAIL all at once to prevent data integrety problems. |
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-09-29 : 12:51:33
|
| put your insert statement in a stored procedure and then use the SCOPE_IDENTITY() function to get the new ID just createdand use it to do the other inserts either in the same procedure or call separate ones.Either that or put a trigger on the table to update the other tables after an insert and/or update |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-29 : 13:55:49
|
| i prefer doing this in procedure rather than in trigger. also make sure you wrap all insert logic in a transaction in procedure to rollback all in case of intermediate failure. |
|
|
|
|
|
|
Insert while using identity to insert other tables