Tuesday, June 12, 2007

Using Autoincrement field Value After Insert

I had the need to insert one record into a SQL Server table that held a unique code and a description of what that code represents. That unique code was automatically generated by the database. Right after that insert I needed to insert several records in another table that used that newly inserted value from the previous table. I found two approaches to do this which I will share below.

Approach 1 - Use @@Identity
DECLARE @AutoIncCode int

INSERT INTO LookupTable (Description)
Values('DescriptionOfItem')

//The @@identity variable holds the last auto incremented value. Store this value or you will lose it after the next insert you do.
SET @@AutoIncCode = @@identity

INSERT INTO DependantTable
(Field1, Field2, LookupTableId)
Values('Val1', 'Val2', @AutoIncCode)

Approach 2 - Fetch from the table you just inserted to
DECLARE @AutoIncCode int

INSERT INTO LookupTable (Description)
Values('DescriptionOfItem')

SET @AutoIncCode = (SELECT LookupTableId
from LookupTable where Description = 'DescriptionOfItem')

INSERT INTO DependantTable
(Field1, Field2, LookupTableId)
Values('Val1', 'Val2', @AutoIncCode)

No comments: