Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Inserting the Same Autonumber into Two Tables 2

Status
Not open for further replies.

Elton1984

Programmer
Mar 8, 2004
16
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
 
From BOL:
@@IDENTITY Returns the last-inserted identity value.

Example:
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

You must capture the value of the @@IDENTITY global variable immediately after the INSERT statement.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)

SELECT @ID = @@IDENTITY

If multiple rows are INSERTed, @@IDENTITY will contain the identity value of the last row inserted.

See also SCOPE_IDENTITY( )

--Angel [rainbow]
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)
 
You can also use IDENT_CURRENT('table') to get the last inserted identity value for a specific table in any scope.

For example:
Code:
CREATE TABLE Test_Table
(
  field1
    int IDENTITY
  ,
  field2
    .....
)

INSERT INTO Test_Table(...)
...

SELECT IDENT_CURRENT('Test_Table')
-- This will return the last inserted value of the identity field "field1"

Hope this helps!

John
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top