SQL - Inserting a row and returning primary key

Question

I have 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?

I should have been more specific and mentioned that I'm currently using SQLite.

Solution 1

For MS SQL Server:

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

SELECT SCOPE_IDENTITY() AS NewID

Solution 2

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)

Solution 3

SQL Server:

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.

Solution 4

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.

Solution 5

If you need to retrieve the new index in MS SQL when there are triggers on the table then you have to use a little workaround. A simple OUTPUT will not work. You have to do something like this (in VB.NET):

DECLARE @newKeyTbl TABLE (newKey INT);
INSERT INTO myDbName(myFieldName) OUTPUT INSERTED.myKeyName INTO @newKeyTbl VALUES('myValue'); " & _
SELECT newKey FROM @newKeyTbl;"

If using .NET, then the return value from this query can be directly cast to an integer (you have to call "ExecuteScalar" on the .NET SqlCommand to get the return).

View more solutions