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
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?
(comments are locked)
|
Katie, You can use OUTPUT Clause to achieve this.
Thank you!
Dec 21, 2010 at 05:44 AM
Katie 1
(comments are locked)
|
Look at OUTPUT Clause in books online, that will solve your issues ah...! You beat me by 1 Sec :)
Dec 20, 2010 at 10:13 PM
Cyborg
(comments are locked)
|
Just as a separate thing - you really shouldn't use This can cause some really odd effects - so try to always use 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)
|
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? mmm... coffee... just a small slice, though!
Dec 21, 2010 at 02:48 AM
ThomasRushton ♦♦
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)
|
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.
Dec 21, 2010 at 05:44 AM
Katie 1
(comments are locked)
|