Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 6 down vote favorite

Good day,

I have a little witty problem. Say, I inserted a row with some data in a table where a primary key is present. How would one "SELECT" the primary key of the row one just inserted?

Thanks in advance.

~Insomnia

share|improve this question
5  
Which RBDMS are you using? – Michael Fredrickson Dec 12 '11 at 18:54

5 Answers

up vote 11 down vote accepted

For MS SQL Server:

SCOPE_IDENTITY() will return you the last generated identity value within your current scope:

SELECT SCOPE_IDENTITY() AS NewID
share|improve this answer
3  
Works for SQL Server - but only if you have a primary key of type INT IDENTITY (or BIGINT IDENTITY) - any other type of PK (like a GUID or something else) is not supported by SCOPE_IDENTITY() – marc_s Dec 12 '11 at 19:01
True, good point... I'll +1 your answer cause you've got that situation covered. – Michael Fredrickson Dec 12 '11 at 19:02
@marc_s: or numeric(x,0) too: guess the type of SCOPE_IDENTITY()... msdn.microsoft.com/en-us/library/ms190315.aspx – gbn Dec 12 '11 at 19:12
Thanks a heap. Although I should have been more specific and mentioned that I'm currently using SQLite. Anyway, I googled up "SCOPE_IDENTITY in SQLite" and found my answer. Cheers. Marking this as answer. – Insomnia Array Dec 13 '11 at 18:26
up vote 7 down vote

For SQL Server 2005 and up, and regardless of what type your primary key is, you could always use the OUTPUT clause to return the values inserted:

INSERT INTO dbo.YourTable(col1, col2, ...., colN)
OUTPUT Inserted.PrimaryKey
VALUES(val1, val2, ....., valN)
share|improve this answer
up vote 2 down vote

For MySQL, use LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

You should also be able to start a transaction, insert the row, and select the row using some field that has a unique value that you just inserted, like a timestamp or guid. This should work in pretty much any RDBMS that supports transactions, as long as you have a good unique field to select the row with.

share|improve this answer
up vote 1 down vote

MS SQL

You can use @@IDENTITY. After an insert statement, you can run:

select @@identity

This will give you the primary key of the record you just inserted. If you are planning to use it later, I suggest saving it:

set @MyIdentity = @identity

If you are using this in a stored procedure and want to access it back in your application, make sure to have nocount off.

share|improve this answer
3  
If you have an IDENTITY column - I would recommend you use SCOPE_IDENTITY() rather than @@IDENTITY – marc_s Dec 12 '11 at 19:19
up vote 0 down vote

For Postgresql:

SELECT CURRVAL(pg_get_serial_sequence('schema.table','id'))

Source: http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.