How to get the identity of an inserted row?
@@IDENTITY
returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.SCOPE_IDENTITY()
returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.IDENT_CURRENT('tableName')
returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."The
OUTPUT
clause of theINSERT
statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.
SQL Server - Return value after INSERT
No need for a separate SELECT...
INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');
This works for non-IDENTITY columns (such as GUIDs) too
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.
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 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.
How get id after inserting a new row of data into the table?
Insert a single row into table distributors, returning the sequence
number generated by the DEFAULT clause:
Code
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;
The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. 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. The syntax of the RETURNING list is identical to that of the
output list of SELECT.
Reference:
- https://www.postgresql.org/docs/9.1/static/sql-insert.html
- https://stackoverflow.com/a/2944481/7793817
Android Room - Get the id of new inserted row with auto-generate
Based on the documentation here (below the code snippet)
A method annotated with the @Insert
annotation can return:
long
for single insert operationlong[]
orLong[]
orList<Long>
for multiple insert operationsvoid
if you don't care about the inserted id(s)
How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?
JdbcTemplate.update()
returns:
the number of rows affected
Which is always 1
for INSERT
statement. Different databases support generated key extraction in different ways, but most JDBC drivers abstract this and JdbcTemplate
supports this. Quoting 12.2.8 Retrieving auto-generated keys
An
update()
convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard; see Chapter 13.6 of the specification for details.
Basically you need this much more verbose statement:
final String insertIntoSql = QueryUtil.getInsertIntoSqlStatement(entity);
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
return connection.prepareStatement(insertIntoSql, new String[] {"id"});
}
}, keyHolder);
return keyHolder.getKey().intValue();
Related Topics
Create a Bitmap/Drawable from File Path
Android Fragmentmanager Backstackrecord.Run Throwing Nullpointerexception
How to Get the Selected Value of a Spinner
Key Hash for Facebook Android Sdk
Capture Screen of Glsurfaceview to Bitmap
Recyclerview Not Call Oncreateviewholder
Can't Get Okhttp's Response.Body.Tostring() to Return a String
Spinner Onitemselected() Executes Inappropriately
Error Inflating Class Com.Google.Ads.Adview
Wait Until Firebase Retrieves Data
How to Parse This JSON in Android
Android:Java.Lang.Outofmemoryerror:
Difference Between Actionbarsherlock and Actionbar Compatibility