0

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!!

mani_nz
  • 4,522
  • 3
  • 28
  • 37

1 Answers1

1

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, ...
D Stanley
  • 149,601
  • 11
  • 178
  • 240