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.

Answer 1

For MS SQL Server:

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

SELECT SCOPE_IDENTITY() AS NewID

Answer 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)

Answer 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.

Answer 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.

Answer 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).

Answer 6

For SQLite:

SELECT [Column_1],  [Column_2],...  [Column_n]
FROM [YourTable]
WHERE rowid = (SELECT last_insert_rowid())

whereas:

• Column_1, Column_2,... Column_n: are the primary key of YourTable.

If you'd created YourTable with primary key replaced rowid (i.e. one column pk defined as INTEGER PRIMARY KEY) you just use:

SELECT last_insert_rowid()

Which is a common case.
Finally, this wont work for WITHOUT_ROWID tables.

Please Check:

https://www.sqlite.org/lang_corefunc.html#last_insert_rowid

Answer 7

For PostgreSQL,

INSERT INTO tablename (col1, col2, ...)
VALUES (val1, val2, ...)
RETURNING idcol;

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed.

https://www.postgresql.org/docs/current/sql-insert.html

Answer 8

For Postgresql:

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

Source: PostgreSQL function for last inserted ID

Answer 9

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.