SQL - Insert and Catch the Id Auto-Increment Value

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.

Catch Id (INT AUTO INCREMENT) of a Record after INSERT INTO Statement

You use the function last_insert_id() in a query to get the last created id:

select last_insert_id()

You have to use the same database session, i.e. the same connection object.

As you have figured out, you should not use select max(id) to get the id, as that will get the last id created by any session, not this specific session. If two inserts are done for separate users close in time (so that both inserts happen before one of them do the select to get the id), they will both get the id of the last 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 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.

get auto increment value for an inserted row safely

as far as I know, the id returned from mysql_insert_id is the auto increment id from the last insert query for the current connection (mysql_connect) to the sql database. The only reason for having to call it right after the query is that if you run two insert queries right after each other, mysql_insert_id returns just the last query. It wouldn't be reliably possible to get the id from the first query. Also, it gets the id from the last query, so if you did an insert then update, then ran mysql_insert_id. It would return 0 because the last query (update) isn't an insert.

Also, php just calls the mysql function mysql_insert_id. From the mysql manual:

The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients.

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

mysql, java - Get last inserted auto increment id from one table and insert it again on another table

Use getGeneratedKeys() method from your Statement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

Change:

stmt.executeUpdate(sql);

To:

int rowsAffected = 
  stmt.executeUpdate( sql, Statement.RETURN_GENERATED_KEYS );  
ResultSet rs = stmt.getGeneratedKeys();  

//******************************************************  
// in case of batch insert, you can check how many are inserted
rs.last();  
int rows = rs.getRow();  
System.out.println( "Generated keys count: " + rows );  
//******************************************************/  

int currentRow = 1;  
rs.beforeFirst();  
while( rs.next() ) {  
    System.out.println( /**/( currentRow++ ) + " = " + /**/rs.getInt( 1 ) );  
} // while rs  

Once you have this auto generated key value in a variable, you can use it with other SQL statements, to store in other tables.

Note: Call to getGeneratedKeys() may throw java.sql.SQLFeatureNotSupportedException, if the JDBC driver, that you are using, does not support this method.

How to get the next auto-increment id in mysql

Use LAST_INSERT_ID() from your SQL query.

Or

You can also use mysql_insert_id() to get it using PHP.

---

Related Topics