question

Katie 1 avatar image
Katie 1 asked

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?
sql-server-2008sqlt-sqlquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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')

[1]: http://msdn.microsoft.com/en-us/library/ms177564.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Look at OUTPUT Clause in books online, that will solve your issues
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ah...! You beat me by 1 Sec :)
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Matt. Thats a good point. I will try to use the Scope_identity().
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the comment Thomas. All are transactions. I just gave a snippet of the code.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mmm... coffee... just a small slice, though!
1 Like 1 ·
+1 you are right, i thought Of that but i was in à hurry and wanted to spread the knowledge about output. :)
0 Likes 0 ·
@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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.