Retrieve identity column value on insert

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

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieve identity column value on insert

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-15 : 11:25:32

I'm trying:

SELECT code, code_description FROM STOCK WHERE(code = 'A123'); SELECT scope_identity AS identity

to return the value of the identity column created by the insert, but I get the error:

Error Message: Incorrect syntax near the keyword 'identity'

Where am I going wrong please?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-15 : 11:39:48

you need the parenthesis:
select scope_identity()

But you also need an actual INSERT statement to generate the identity value - a SELECT won't do it.

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-15 : 12:09:00

where's the insert?
in sql2005 use OUTPUT to get recent identity value created

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-15 : 12:37:07

OK, sorry about the insert thing. It's been a long day, what can I say?

So, if I want to return the identity field as identity would:

"INSERT INTO stock (code, code_description) VALUES ('SC22X10', 'Screw - 22mm x 10mm'); SELECT scope_identity() OUTPUT AS identity"

be correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 12:42:26

quote:
Originally posted by OldMySQLUser

OK, sorry about the insert thing. It's been a long day, what can I say?

So, if I want to return the identity field as identity would:

"INSERT INTO stock (code, code_description) VALUES ('SC22X10', 'Screw - 22mm x 10mm'); SELECT scope_identity() OUTPUT AS identity"

be correct?

either

INSERT INTO stock (code, code_description)
VALUES ('SC22X10', 'Screw - 22mm x 10mm');

SELECT scope_identity() AS identity

or

INSERT INTO stock (code, code_description) OUTPUT INSERTED.pk
VALUES ('SC22X10', 'Screw - 22mm x 10mm');

where pk is ID col in stock

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-15 : 12:56:41

Many thanks visakh16.

The second 'OUTPUT' method worked for me. SQL 2005 didn't like the first method.

Something new learned on my sql quest ... :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources