SQL: How to get the identifier of the values ​​I just inserted?

Question

I inserted some values ​​into the table. There is a column whose value is auto-generated. In the following expression of my code, I want to get this value.

Can you tell me how to do it right?

Answer 1

@@IDENTITY not safe for the region and will return you an identifier from another table, if you have an insert trigger in the source table, always use SCOPE_IDENTITY()

Answer 2

This is how I do my storage procedures for MSSQL with an auto-generated identifier.

 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(); 

Answer 3

If you use PHP and MySQL, you can use the mysql_insert_id () function, which will tell you the identifier of the item you just contributed.
But without your language and DBMS, I just shoot in the dark.

Answer 4

This works very well 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) 

This makes it possible to return all identifiers if the INSERT statement inserts multiple rows.

Answer 5

Again not an agnostic language response, but in Java it looks 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(); } 

Answer 6

If you are working with Oracle:

Insert into the table (fields ...) values ​​(values ​​...) RETURN (list of fields ...) INTO (variables ...)

Example:

INSERT PERSON (TITLE) VALUES ("JACK") RETURN ID_PERSON INTO vIdPerson

or if you are calling from ... Java using CallableStatement (sry, it my field)

INSERT IN MAN (NAME) VALUES ("JACK") RETURN OF ID_PERSON INTO?

and declaration of the autput parameter for the operator

Answer 7

There is no standard way to do this (just as there is no standard way to create auto-increment identifiers). Here are two ways to do this in PostgreSQL. Suppose this is your table:

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

You can do this in two operations if they are consecutive statements in the same connection (this will be safe in PHP with a connection pool, since PHP does not return the connection to the pool until your script is done):

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

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

Another way is to use the PostgreSQL RETURNING clause in INSERT :

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

This form returns the result set in the same way as the SELECT statement, and is also convenient for returning any calculated default value.

Answer 8

It is important to note that using supplier SQL queries to retrieve the last inserted identifier is safe to use without fear of concurrent connections.

I always thought that you need to create a transaction to insert a row, and then SELECT the last inserted identifier to avoid getting the identifier inserted by another client.

But these vendor-specific queries always retrieve the last inserted identifier for the current database connection . This means that the last inserted identifiers cannot be affected by other client inserts if they use their own connection to the database.

Answer 9

From the site I learned about the following things:

SQL SERVER - @@ IDENTITY vs SCOPE_IDENTITY () vs IDENT_CURRENT - Get the last inserted identity record March 25, 2007 pinaldave

SELECT @@ IDENTITY It returns the last IDENTITY value created in the join, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@ IDENTITY will return the last identifier value entered in the table of the current session. Although @@ IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger in a table that forces you to create an identity in another table, you will get the identifier that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY () It returns the last IDENTITY value created in the join and by the statement in the same scope, regardless of the table that generated the value. SCOPE_IDENTITY (), for example @@ IDENTITY, will return the last authentication value created in the current session, but it will also limit it to your current scope. In other words, it will return the last identifier value that you explicitly created, and not any identity created by a trigger or user-defined function.

SELECT IDENT_CURRENT ('tablename) It returns the last IDENTITY value created in the table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited to scope and session; it is limited to the specified table. IDENT_CURRENT returns the identifier value generated for a particular table in any session and in any scope.

Answer 10

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

The value of the ID column will be returned.

Answer 11

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

Answer 12

What database are you using? As far as I know, there is no agnostic database method for this.

Answer 13

Here's how I did 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(); 

Answer 14

 sql = "INSERT INTO MyTable (Name) VALUES (@Name);" + "SELECT CAST(scope_identity() AS int)"; SqlCommand cmd = new SqlCommand(sql, conn); int newId = (int)cmd.ExecuteScalar(); 

Answer 15

Ms SQL Server: this is a good solution even if you insert more rows:

  Declare @tblInsertedId table (Id int not null) INSERT INTO Test ([Title], [Text]) OUTPUT inserted.Id INTO @tblInsertedId (Id) SELECT [Title], [Text] FROM AnotherTable select Id from @tblInsertedId 

Answer 16

Rob's answer will be the most agronizing agent, but if you use MySQL , the safer and more appropriate choice will be the built-in LAST_INSERT_ID() function.

Answer 17

 SELECT @@Scope_Identity as Id 

There is also the @@ identifier, but if you have a trigger, it will return the results of what happened during startup, where scope_identity respects your area.

Answer 18

• enter a string with a known pointer.
• enter the autoId field with this director.

This should work with any database.

Answer 19

Oracle solution based on development environment:

 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 

Answer 20

In TransactSQL, you can use the OUTPUT clause to achieve this.

 INSERT INTO my_table(col1,col2,col3) OUTPUT INSERTED.id VALUES('col1Value','col2Value','col3Value') 

FRI: http://msdn.microsoft.com/en-us/library/ms177564.aspx

Answer 21

The simplest answer:

 command.ExecuteScalar() 

returns the first column by default

Return Value Type: System.Object The first column of the first row in the result set, or null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.

Copied from MSDN