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 |
|
ron2112
Starting Member
44 Posts |
 Posted - 2007-02-07 : 16:14:12
|
| Sorry if this is an obvious n00b question. I have a number of stored procedures where I'm adding a record to a main table with an identity column, then adding related records to one or more secondary tables. The identity column is used in the FK relationship between the main and secondary tables.What I want to know is the proper way of obtaining the identity value of the main table record I added, so I can use that value in the records I'm adding to the secondary tables. Right now I'm doing it like this...INSERT INTO tbl_MAIN (Name)VALUES ("Bob")DECLARE @Identity int;SET @Identity = SELECT MAX(RecNum) FROM tbl_MAIN;INSERT INTO tbl_SECONDARY (MAINRecNum, DogsName)VALUES (@Identity, "Fido")So basically I'm adding the main record, then getting the MAX() identity value from the main table, under the assumption that the one I just added should be the max. My concern is that, when many users are in the system at once, another transaction might sneak in between the first insert and that select statement, and I'll get the wrong identity value.Is there a more proper way of getting that value?Thanks!Ron MosesConEst Software Systems |
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-02-07 : 16:22:24
|
| After you insert the record into the main table, do SELECT SCOPE_IDENTITY() to return the last identity value given out by that session.Hope this helps.Thanks,Rich |
 |
|
|
ron2112
Starting Member
44 Posts |
Posted - 2007-02-07 : 16:28:16
|
| That is EXACTLY what I'm looking for, I just couldn't find the syntax. Thank you SO much!!!Ron MosesConEst Software Systems |
|
|
|
|
|
|
Getting the identity value of a new record