x

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?
more ▼

asked Dec 20, 2010 at 09:34 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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
more ▼

answered Dec 20, 2010 at 10:00 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

Thank you!
Dec 21, 2010 at 05:44 AM Katie 1
(comments are locked)
10|1200 characters needed characters left
Look at OUTPUT Clause in books online, that will solve your issues
more ▼

answered Dec 20, 2010 at 10:00 PM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

ah...! You beat me by 1 Sec :)
Dec 20, 2010 at 10:13 PM Cyborg
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Dec 21, 2010 at 12:08 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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)
10|1200 characters needed characters left
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?
more ▼

answered Dec 21, 2010 at 02:30 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

mmm... coffee... just a small slice, though!
Dec 21, 2010 at 02:48 AM ThomasRushton ♦
+1 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)
10|1200 characters needed characters left
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.
more ▼

answered Dec 21, 2010 at 01:23 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x991
x738
x375

asked: Dec 20, 2010 at 09:34 PM

Seen: 3466 times

Last Updated: Dec 20, 2010 at 09:34 PM