Obtaining the recently inserted value in an non identity column.
Hello,
I want to know if there is a way to get the recently inserted priomary key id, in a non 'identity column',for example, if we look at the below example
Declare @id int
insert into A.object (@id, 'name')
select @id = @@IDENTITY from A.object
insert into A.object2 (@id,name2,additionalColumn)
select @id = @@IDENTITY from A.object
insert a.object3(@id,name3,additional column)
Till this part might work fine, if the object2, has a identity column, but in my case if i have to propagate the same @id to the from object2 to object3, with object2 not having any idnetity column, how can i work with it. At this point its throwing an error as, A.Object2 does not have an identity column..
How can one get the recently inserted value in the object2 and pass that value to object3?
Best Answer
Katie, You can use [OUTPUT][1] Clause to achieve this.
Eg: Declare @Object2 Table(ID INT)
Declare @id int
insert into A.object (@id, 'name')
select @id = @@IDENTITY from A.object
insert into A.object2
OUTPUT INSERTED.ID into @Object2 -- Get the Inserted ID Value from Object2
VALUES (@id,'name2','additionalColumn')
-- Inserting the recently inserted PK value of Object2 to Object3
insert a.object3(@id,'name3','additional column')
Look at OUTPUT Clause in books online, that will solve your issues
Just as a separate thing - you really shouldn't use `@@IDENTITY`. `@@IDENTITY` returns 'the last inserted `IDENTITY` value on the connection' - meaning that if you do an insert into a table, and that table has a trigger which inserts into another table with an `IDENTITY` column, then `@@IDENTITY` will return the `IDENTITY` value that the trigger created, not the value assigned to the row you inserted.
This can cause some really odd effects - so try to always use `scope_identity()` instead.
Thank you Matt. Thats a good point. I will try to use the Scope_identity().
I might be missing something here, or maybe it's lack of coffee, but aside from the good answers about OUTPUT clause, if the PK on object2 isn't an identity field then I presume you are setting the value somewhere in code - so if you know what value is being used, then you know what value to use for the insert into object3?
+1 you are right, i thought Of that but i was in à hurry and wanted to spread the knowledge about output. :)
@kev, there is is same logic happening when we enter the value from the frontend, but there were few records that are to be populated manually so i decided to take this approach. The fashion that the code populates the data is above described. As per the database design, the identity value generated on the first object itself is being propagated to another two levels of tables under the object1.And, the identity value generated in the object1, holds the reference for each record.
Another side-issue. If you are writing code that's dependent upon this sort of thing, then make sure your schema includes appropriate foreign-key relationships, and wrap up your T-SQL statements in transactions.
Thanks for the comment Thomas. All are transactions. I just gave a snippet of the code.
