Return Primary Key on Insertion of record?

[ryandoah]

Does anyone know how or if it is possible to return the primary key of a newly inserted record at execution?

Thank you,

ryandoah

 

[Rhys666]

After the insert statement, use

SELECT SCOPE_IDENTITY()

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese

 

[SQLBI]

If the PK is an IDENTITY Column, you could also use SELECT @@IDENTITY following your INSERT.





Cheers,
Leigh

You're only as good as your last backup!

 

[ryandoah]

Excellent, that's the kickstart I needed. I've googled this and found that it is not SQLServer 7 compatible, which is a problem, but it's a start.

 

[ryandoah]

If the PK is an IDENTITY Column, you could also use SELECT @@IDENTITY following your INSERT.

Do you know if this method is SQL 7 friendly?

 

[jbenson001]

Try it and you will find out.

 

[ryandoah]

Because Leigh had just made the suggestion as an answer to my question, which means Leigh was probably still online, which means I MIGHT be able to get an answer to my next question before she/he logs back out, which I didn't, but it's a chance worth taking, huh? The first response I received set me on the proper track, but as soon as I Googled it, I found it was for SQL2000+. I could not find the same type of info about @@IDENTITY, so I asked. No problem in asking.

I'm sorry if this has bothered you, but I don't think it was out of line for me to ask the question, nor do I think that I have demonstrated behavior that would make me appear as if I wouldn't try it myself, I just hadn't yet had an opportunity to do so at the time, and it was easier to type in my question and try for a response.

Thanks for your help.

 

[jbenson001]

Both suggestings made here
Scope_Identity() AND
@@Identity

Will work in SQL Server 6.5 and up.

 

[SQLBI]

Now now guys, chill!!!

ryandoah,

I've no idea aout SQL 7, definitely 2k though.

to check, run the following code, if QA returns 3 then it works for SQL 7 too.

CREATE TABLE t (id int IDENTITY(1, 1) PRIMARY KEY, col2 varchar(10))

INSERT t (col2)
VALUES('first')


INSERT t (col2)
VALUES('second')


INSERT t (col2)
VALUES('third')

select @@Identity

DROP TABLE t

Hope this helps.




Cheers,
Leigh

You're only as good as your last backup!

 

[ryandoah]

Thank you.

Though I contest that Scope_Identity() will work in SQL7, as reference above. And, yes, I tested it. No dice.

@@IDENTITY, on the other hand, does work.

 

[SQLBI]

Now if i'd refreshed my browser sooner i'd have saveed some time... lol

Cheers,
Leigh

You're only as good as your last backup!

 

[jbenson001]

Interesting, MDSN made no mention of version issues relating to them. But at least you have @@Identity to use.

 

[SQLBI]

A word of warning @@DENTITY will return the last identity value so in my example, to return each subsequnt PK, you'd need to include SELECT @@IDENTITY after each insert.

Hope this helps.

Cheers,
Leigh

You're only as good as your last backup!

 

[ryandoah]

Yeah, it kills the query.

Server: Msg 195, Level 15, State 10, Line 3
'SCOPE_IDENTITY' is not a recognized function name.

 

[SQLBI]

Sorry, @@IDENTITY... no idea if @@DENTITY will work in any version of SQL!

I need a cup of tea!

Cheers,
Leigh

You're only as good as your last backup!

 

[ryandoah]

lol. thanks.

 

[SQLSister]

And be careful with @@identity (I realize that in SQL Server 7 you can't use scope_identity()). If you add a trigger to a table that also inserts an identity value, that is the value that will be returned by @@identity. THis can give you major data integrity problems.

If there is something else unique about the record you might want to consider using that to select the record instead.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief