INSERT @@identity at once

[anselme]

Hi

how can I insert @@identity at once (for MS SQL 2000 / 2500)

INSERT INTO Table1 (name) VALUES ('any')

DECLARE @ID int SELECT @@Identity

INSERT INTO Table2 (id_Table1) VALUES (@ID)

does not work

thank you

[Faris]

I'm using SQL Server 7 (currently migrating to 2005), but this little change worked for me:

INSERT INTO Table1 (name) VALUES ('any')

DECLARE @ID int 
SET @ID = @@Identity 

INSERT INTO Table2 (id_Table1) VALUES (@ID)

You may also want to consider (if you havent done so already) putting the 2 inserts into a transaction. This will allow you to rollback the first insert if the second one fails for any reason. Depends on your needs.

Hope this helps,

Faris

[skhanal]

You have to capture @@identity immediately after insert.

DECLARE @ID int

INSERT INTO Table1 (name) VALUES ('any')

SET @ID = @@Identity

INSERT INTO Table2 (id_Table1) VALUES (@ID)

[rmiao]

Better to use SCOPE_IDENTITY( ) instead of @@Identity.

[anselme]

DECLARE @ID int

INSERT INTO Table1 (name) VALUES ('any')

SET @ID = @@Identity

INSERT INTO Table2 (id_Table1) VALUES (@ID)


works perfectly

DECLARE @ID int

INSERT INTO Table1 (name) VALUES ('any')

SET @ID = SCOPE_IDENTITY( )

INSERT INTO Table2 (id_Table1) VALUES (@ID)

i get an error on SCOPE_IDENTITY( ) invalid


thank you

[rmiao]

Ran your code on my sql2k sp4 server without error. Keep in mind that SCOPE_IDENTITY( ) returns the last IDENTITY value inserted into an IDENTITY column in the same scope while @@Identity not limited to a specific scope.

[anselme]

rmiao I dont get it

The name "SCOPE_IDENTITY" is not permitted in this context. Valid expressions are constants, constant expressions


INSERT INTO Table1 (name)
VALUES ('any')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

INSERT INTO Table2 (id_Table1)
VALUES ([SCOPE_IDENTITY])

does not work at all

How can I do it ?
thank you

[rmiao]

The code you posted before works for me, that's:

DECLARE @ID int

INSERT INTO Table1 (name) VALUES ('any')

SET @ID = SCOPE_IDENTITY( )

INSERT INTO Table2 (id_Table1) VALUES (@ID)