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:
Post a Comment