Insert, then select scope_identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert, then select scope_identity

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2009-03-12 : 15:58:01

I'm trying to insert a record, select the scope_identity for that record that was just insert, then use that value in another insert. But I'm getting errors. Here is my schema:

-------------------------------------------------
Orders
-------------------------------------------------
OrderID | OrderDate
-------------------------------------------------

-------------------------------------------------
Transactions
-------------------------------------------------
TransactionID | OrderID
-------------------------------------------------

And here is my SQL query I'm using to try and do this:

DECLARE @OrderID int

INSERT INTO Orders
VALUES ('03/12/2009')
GO
SELECT @OrderID = SCOPE_IDENTITY()

INSERT INTO Transactions
VALUES (@OrderID)

Here is the output:

(1 row(s) affected)
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@OrderID".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@OrderID".

As you can see, the order gets inserted, but it can't recognize my variable when I try to insert the transaction.

What am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 16:55:12

The GO keyword denotes a new batch, and as such all variables are flushed.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 16:55:47

[code]DECLARE @OrderID int

INSERT INTO Orders
VALUES ('03/12/2009')

SELECT @OrderID = SCOPE_IDENTITY()

INSERT INTO Transactions
VALUES (@OrderID)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources