SQL: How to get the id of values I just INSERTed?

@@IDENTITY is not scope safe and will get you back the id from another table if you have an insert trigger on the original table, always use SCOPE_IDENTITY()

This is how I do my store procedures for MSSQL with an autogenerated ID.

CREATE PROCEDURE [dbo].[InsertProducts]
    @id             INT             = NULL OUT,
    @name           VARCHAR(150)    = NULL,
    @desc           VARCHAR(250)    = NULL

AS

    INSERT INTO dbo.Products
       (Name,
        Description)
    VALUES
       (@name,
        @desc)

    SET @id = SCOPE_IDENTITY();

This works very nicely in SQL 2005:

DECLARE @inserted_ids TABLE ([id] INT);

INSERT INTO [dbo].[some_table] ([col1],[col2],[col3],[col4],[col5],[col6])
OUTPUT INSERTED.[id] INTO @inserted_ids
VALUES (@col1,@col2,@col3,@col4,@col5,@col6)

It has the benefit of returning all the IDs if your INSERT statement inserts multiple rows.

If your using PHP and MySQL you can use the mysql_insert_id() function which will tell you the ID of item you Just instered.
But without your Language and DBMS I'm just shooting in the dark here.

Again no language agnostic response, but in Java it goes like this:

Connection conn = Database.getCurrent().getConnection();  
PreparedStatement ps =  conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
try {  
    ps.executeUpdate();  
    ResultSet rs = ps.getGeneratedKeys();  
    rs.next();  
    long primaryKey = rs.getLong(1);  
} finally {  
    ps.close();  
}  

If you are working with Oracle:

Inset into Table (Fields....) values (Values...) RETURNING (List of Fields...) INTO (variables...)

example:

INSERT INTO PERSON (NAME) VALUES ('JACK') RETURNING ID_PERSON INTO vIdPerson

or if you are calling from... Java with a CallableStatement (sry, it's my field)

INSERT INTO PERSON (NAME) VALUES ('JACK') RETURNING ID_PERSON INTO ?

and declaring an autput parameter for the statement

There's no standard way to do it (just as there is no standard way to create auto-incrementing IDs). Here are two ways to do it in PostgreSQL. Assume this is your table:

CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  lastname VARCHAR NOT NULL,
  firstname VARCHAR
);

You can do it in two statements as long as they're consecutive statements in the same connection (this will be safe in PHP with connection pooling because PHP doesn't give the connection back to the pool until your script is done):

INSERT INTO mytable (lastname, firstname) VALUES ('Washington', 'George');
SELECT lastval();

lastval() gives you the last auto-generated sequence value used in the current connection.

The other way is to use PostgreSQL's RETURNING clause on the INSERT statement:

INSERT INTO mytable (lastname) VALUES ('Cher') RETURNING id;

This form returns a result set just like a SELECT statement, and is also handy for returning any kind of calculated default value.

An important note is that using vendor SQL queries to retrieve the last inserted ID are safe to use without fearing about concurrent connections.

I always thought that you had to create a transaction in order to INSERT a line and then SELECT the last inserted ID in order to avoid retrieving an ID inserted by another client.

But these vendor specific queries always retrieve the last inserted ID for the current connection to the database. It means that the last inserted ID cannot be affected by other client insertions as long as they use their own database connection.

For SQL 2005:

Assuming the following table definition:

CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [somevalue] [nchar](10) NULL,
) 

You can use the following:

INSERT INTO Test(somevalue)
OUTPUT INSERTED.ID
VALUES('asdfasdf')

Which will return the value of the ID column.

Remember that @@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You should always use SCOPE_IDENTITY() to return the identity of the recently added row.

What database are you using? As far as I'm aware, there is no database agnostic method for doing this.

This is how I've done it using parameterized commands.

MSSQL

 INSERT INTO MyTable (Field1, Field2) VALUES (@Value1, @Value2); 
 SELECT SCOPE_IDENTITY(); 

MySQL

 INSERT INTO MyTable (Field1, Field2) VALUES (?Value1, ?Value2);
 SELECT LAST_INSERT_ID();

Rob's answer would be the most vendor-agnostic, but if you're using MySQL the safer and correct choise would be the built-in LAST_INSERT_ID() function.

SELECT @@Scope_Identity as Id

There is also @@identity, but if you have a trigger, it will return the results of something that happened during the trigger, where scope_identity respects your scope.

1. insert the row with a known guid.
2. fetch the autoId-field with this guid.

This should work with any kind of database.

An Environment Based Oracle Solution:

CREATE OR REPLACE PACKAGE LAST
AS
ID NUMBER;
FUNCTION IDENT RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY LAST
AS
FUNCTION IDENT RETURN NUMBER IS
    BEGIN
    RETURN ID;
    END;
END;
/


CREATE TABLE Test (
       TestID            INTEGER ,
    Field1      int,
    Field2      int
)


CREATE SEQUENCE Test_seq
/
CREATE OR REPLACE TRIGGER Test_itrig
BEFORE INSERT ON Test
FOR EACH ROW
DECLARE
seq_val number;
BEGIN
IF :new.TestID IS NULL THEN
    SELECT Test_seq.nextval INTO seq_val FROM DUAL;
    :new.TestID := seq_val;
    Last.ID := seq_val;
END IF;
END;
/

To get next identity value:
SELECT LAST.IDENT FROM DUAL