Obtaining the recently inserted value in an non identity column.

Question

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?

Answer 1

0

Katie,

You can use OUTPUT 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')

answered Dec 20, 2010 at 10:00 PM

Cyborg
10.8k ● 37 ● 54 ● 51

Thank you!

Dec 21, 2010 at 05:44 AM Katie 1

(comments are locked)

Answer 2

0

Look at OUTPUT Clause in books online, that will solve your issues

answered Dec 20, 2010 at 10:00 PM

Håkan Winther
16.5k ● 36 ● 45 ● 57

ah...! You beat me by 1 Sec :)

Dec 20, 2010 at 10:13 PM Cyborg

(comments are locked)

Answer 3

0

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.

answered Dec 21, 2010 at 12:08 AM

Matt Whitfield ♦♦
29.5k ● 62 ● 66 ● 88

Thank you Matt. Thats a good point. I will try to use the Scope_identity().

Dec 21, 2010 at 05:42 AM Katie 1

(comments are locked)

Answer 4

0

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?

answered Dec 21, 2010 at 02:30 AM

Kev Riley ♦♦
64k ● 48 ● 61 ● 81

mmm... coffee... just a small slice, though!

Dec 21, 2010 at 02:48 AM ThomasRushton ♦♦

you are right, i thought Of that but i was in à hurry and wanted to spread the knowledge about output. :)

Dec 21, 2010 at 04:54 AM Håkan Winther

@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.

Dec 21, 2010 at 05:41 AM Katie 1

(comments are locked)

Answer 5

0

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.

answered Dec 21, 2010 at 01:23 AM

ThomasRushton ♦♦
39.9k ● 20 ● 49 ● 52

Thanks for the comment Thomas. All are transactions. I just gave a snippet of the code.

Dec 21, 2010 at 05:44 AM Katie 1

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Obtaining the recently inserted value in an non identity column.

Follow this question

Related Questions