SQL - Inserting a row and returning primary key

问题:

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?

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

回答1:

For MS SQL Server:

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

SELECT SCOPE_IDENTITY() AS NewID

回答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)

回答3:

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.

回答4:

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.

回答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).

回答6:

For Postgresql:

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

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

回答7:

select MAX(id_column) from table

That, in theory, should return you that last inserted id. If it's a busy database with many inserts going on it may not get the one you just did but another.

Anyhow, an alternative to other methods.