Insert and Select in 1 statement - help

[ttohme]

Hi,

I have 2 tables that have a relationship based on a primary id key which is auto generated.

In my application I insert a new row in the first table and the number is auto generated, but when I want to insert the related data in the second table I will need the value of the generated number from the first table.

Is there a way that the insert statement against the first table returns a columns of the new row that represent the ID generated so I wouldn't have to do a select on all the columns to retreive this number.?

Thanks

[dglienna]

Which DB are you using?

[jp140768]

You may be able to use the @@Identity function?

[Erik Wiggins]

If your using access be sure that the

Enforce Referential Integrity
Cascade Update Related Rields

checkboxes are selected in your releationship.

Also if you want both to be deleted when one or the other is deleted then make sure the

Cascade Delete Related Records

checkbox is selected.

Hope this helps

[senthilraj]

u can use
declare @id int
insert .............

select @id=@@identity
u will get the auto generated value in table 1

[pranay]

guys!!
@@identity is not going to solve the purpose as it is not session specific... to get teh correct valye go with scope_Identity().

Hope this helps!!

[hspc]

guys!!
@@identity is not going to solve the purpose as it is not session specific... to get teh correct valye go with scope_Identity().

@@identity gets the value on the session level.
Check MSDN:

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope

[cjard]

Gah.. all you guys went off on giving SQL Server advice without it ever being confirmed that the OP was actually using SQL Server.. Let's hope he was!

[hspc]

Gah.. all you guys went off on giving SQL Server advice without it ever being confirmed that the OP was actually using SQL Server.. Let's hope he was!

He did not confirm the DBMS he uses, anyway, the thread turned to discuss the replies more than the question