Inserting the Same Autonumber into Two Tables 4

[Elton1984]

Hi all,
I have a problem here where i am supposed to insert records into 2 tables.
Table A has a primary key (loanindex) which is an autonumber. And i need to get the loanindex of the just inserted record and insert it into Table B.
I have seen some forums, and notice that's a method regarding "@@identity".. I would like to know how do u go around using that method without the used of a stored procedure...
Codes snipplets would be greatly appericated...

Thanx in advance
Elton

 

[NathanGriffiths]

Hi you want to do something like this;

[green]-- this declares a local variable to hold the identity value[/green]
DECLARE @LastIdentity INT

[green]-- insert a row of data into TableA, the identity column will automatically generate a new identity value[/green]
INSERT INTO TableA VALUES (...)

[green] -- set the variable to hold the value of the last inserted identity in TableA, given by the system variable @@Identity[/green]
SET @LastIdentity = @@Identity

[green]-- Insert the identity value (and any other values)into TableB[/green]
INSERT INTO TableB (LoanIndex, ..) VALUES (@LastIdentity, ..)

Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874

 

[suoirotciv]

I just want to thank you BlueStringPudding for this helpful post

Please pardon the grammar.
Not good in english.

 

[SQLSister]

I have to srep in here and point out that you do NOT want to use @@identity if you care about data integrity. Use scope_identity() instead. Why? BEcause @@identity can give the wrong value if there is a trigger onthe table which inserts a record to another table with an idnetity field. You don;t have a trigger, so it's no problem? Wrong, how can you guarantee that you will never have a trigger? Again, NEVER use @@Identity. Ever!

SET @LastIdentity = Scope_identity()

 

[ESquared]

Amen, sister, everyone listen to SQLSister and may I say again, listen to SQLSister

 

[TysonLPrice]

Heh heh...glad I try to read this forum each day. I use @@Identity all over the place. Now I know better.