Get the Auto-Generated Id After an Insert

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

How to get the generated ID of an auto increment column after insertion of a record in HSQLDB within same connection?

You can use the identity() method in the next INSERT statement for the child table. In this example the child table has a ref_id column for the foreign key plus a data_col for its data. The child table has its own primary key column and a foreign key column that references the log_id column of the test table.

create table child(child_id integer identity primary_key, ref_id integer, data_col varchar);
alter table child add constraint child_fk foreign key (ref_id) references test(log_id);
insert into child(ref_id, data_col) values (identity(), ?);

Get auto-generated ID from INSERT by Slick Plain SQL

Finally I use the way as (insertTaskAction andThen selectTaskIdAction).transactionally, although transactionally may not quite necessary.

Retrieve Auto Increment ID from SQL Database after Insert Statement

By referring sample answer from @Mx.Wolf, I modified a bit to get the right answer, below here is the codes that is working :

     protected void btn_Request(object sender, EventArgs e)
    {
        object id ;
        string insertCmd = "INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName) " +
            "output inserted.PRReqID " +
            "VALUES (@RequestTo,@RequestFrom,@RequestedByName)";

        using (SqlConnection conn = new SqlConnection(cs))
        {
            conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(insertCmd, conn))
            {
                sqlcmd.Parameters.AddWithValue("@RequestTo", lblPurchasingDept.Text);
                sqlcmd.Parameters.AddWithValue("@RequestFrom", ddlDept.SelectedItem.Text);
                sqlcmd.Parameters.AddWithValue("@RequestedByName", SUserName.Text);

                id = sqlcmd.ExecuteScalar(); //the result is of Object type, cast it safely
            }
        }

        Debug.WriteLine(id.ToString()); // Access it like this

Get generated id after insert

The insert method returns the id of row just inserted or -1 if there was an error during insertion.

long id = db.insert(...);

where db is SQLiteDatabase.

How to autogenerate ID during sql INSERT?

Just omit the id column and it will be generated automatically for a serial column.

You need to add a column list with all columns except the id column:

INSERT INTO table_having_pk (col1, col2, ...)
SELECT col1, col2, ... FROM table_without_pk;

If your column does not have a default from a sequence (like a serial would), you can also drop the id column from both tables and:

INSERT INTO table_having_pk
SELECT * FROM table_without_pk;

Then add a serial column:

ALTER TABLE table_having_pk ADD COLUMN table_having_pk_id serial PRIMARY KEY;

Numbers are auto-generated, so this will take a moment. Every row is rewritten.

Or you generate the numbers on the fly, if you don't want a serial (or can't have one):

INSERT INTO table_having_pk (id, col1, col2, ...)
SELECT row_number() OVER (), col1, col2, ...
FROM table_without_pk;

Get the auto-generated ID after an insert

You can get the returning id into a variable. For example, this code:

$data = array("larry","bill","steve");
$db = OCILogon("scott","tiger");
$stmt = OCIParse($db,"insert into names values (myid.nextval,:name) returning id into :id");

OCIBindByName($stmt,":ID",$id,32);
OCIBindByName($stmt,":NAME",$name,32);

while (list(,$name) = each($data))
{
     OCIExecute($stmt);
     echo "$name got id:$id\n"; 
}

This gives you the ID got by $name in form of the variable $id. Change your SQL accordingly.

How to get the value of autoincrement of last row at the insert

Use SCOPE_IDENTITY:

-- do insert

SELECT SCOPE_IDENTITY();

Which will give you:

The last identity value inserted into an identity column in
the same scope. A scope is a module: a stored procedure, trigger,
function, or batch. Therefore, two statements are in the same scope if
they are in the same stored procedure, function, or batch.

SQL - INSERT and catch the id auto-increment value

It depends on your database server. Using MySQL, call mysql_insert_id() immediately after your insert query. Using PostgreSQL, first query "select nextval(seq)" on the sequence and include the key in your insert query.

Querying for "select max(id) + 1 from tbl" could fail if another request inserts a record simultaneously.

---

Related Topics