Search code examples
sqlsybasesap-ase

Using @@identity for consecutive inserts

I have this situation,

INSERT INTO TABLE1()...
--Get the primary key from the above insert
SELECT @@identidy
INSERT INTO TABLE2()...

The auto generated primary key has to be a foreign key in TABLE 2. How can I construct my second INSERT to have the value of @@identity?

This doesn't seem to work,

INSERT INTO TABLE1 (user_id, name) (@@identity, 'ABC')

I get an error saying Must declare variable '@@identidy'.

Cheers!!

Solution

  • 1) you spelled @@identity wrong (@@identidy)

    2) You should create a local variable (@LastIdentity) to store the last inserted identity immediately after the first insert. Then use that variable as the input to the second INSERT:

    DECLARE @LastIdentity int

INSERT INTO TABLE1()...
--Get the primary key from the above insert

SELECT @LastIdentity = @@identity

INSERT INTO TABLE2(...) VALUES (@LastIdentity, ...