<p>I inserted some values into a table. There is a column whose value is auto-generated. In the next statement of my code, I want to retrieve this value.</p>

<p>Can you tell me how to do it the right way?</p>

[{'answer_id': 45657, 'author': 'DevelopingChris', 'author_id': 1220, 'author_profile': 'https://Stackoverflow.com/users/1220', 'pm_score': 0, 'selected': False, 'text': '<pre><code>SELECT @@Scope_Identity as Id\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 45659, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 45662, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 2, 'selected': False, 'text': "<p>What database are you using? As far as I'm aware, there is no database agnostic method for doing this. </p>\n"}, {'answer_id': 45665, 'author': 'David Basarab', 'author_id': 2469, 'author_profile': 'https://Stackoverflow.com/users/2469', 'pm_score': 5, 'selected': False, 'text': '<p>This is how I do my store procedures for MSSQL with an autogenerated ID.</p>\n\n<pre><code>CREATE PROCEDURE [dbo].[InsertProducts]\n    @id             INT             = NULL OUT,\n    @name           VARCHAR(150)    = NULL,\n    @desc           VARCHAR(250)    = NULL\n\nAS\n\n    INSERT INTO dbo.Products\n       (Name,\n        Description)\n    VALUES\n       (@name,\n        @desc)\n\n    SET @id = SCOPE_IDENTITY();\n</code></pre>\n'}, {'answer_id': 45666, 'author': 'UnkwnTech', 'author_id': 115, 'author_profile': 'https://Stackoverflow.com/users/115', 'pm_score': 4, 'selected': False, 'text': "<p>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.<br />\nBut without your Language and DBMS I'm just shooting in the dark here.</p>\n"}, {'answer_id': 45667, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 7, 'selected': True, 'text': '<p><code>@@IDENTITY</code> 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 <code>SCOPE_IDENTITY()</code></p>\n'}, {'answer_id': 45682, 'author': 'Tobias Baaz', 'author_id': 3036, 'author_profile': 'https://Stackoverflow.com/users/3036', 'pm_score': 1, 'selected': False, 'text': "<p>Rob's answer would be the most vendor-agnostic, but if you're using <strong>MySQL</strong> the safer and correct choise would be the built-in <code>LAST_INSERT_ID()</code> function.</p>\n"}, {'answer_id': 45743, 'author': 'Slartibartfast', 'author_id': 4433, 'author_profile': 'https://Stackoverflow.com/users/4433', 'pm_score': 4, 'selected': False, 'text': '<p>Again no language agnostic response, but in Java it goes like this:  </p>\n\n<pre><code>Connection conn = Database.getCurrent().getConnection();  \nPreparedStatement ps =  conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);\ntry {  \n    ps.executeUpdate();  \n    ResultSet rs = ps.getGeneratedKeys();  \n    rs.next();  \n    long primaryKey = rs.getLong(1);  \n} finally {  \n    ps.close();  \n}  \n</code></pre>\n'}, {'answer_id': 45746, 'author': 'Telcontar', 'author_id': 518, 'author_profile': 'https://Stackoverflow.com/users/518', 'pm_score': 3, 'selected': False, 'text': "<p>If you are working with Oracle:</p>\n\n<blockquote>\n  <p>Inset into Table (Fields....) values (Values...) RETURNING (List of Fields...) INTO (variables...)</p>\n</blockquote>\n\n<p>example:</p>\n\n<blockquote>\n  <p>INSERT INTO PERSON (NAME) VALUES ('JACK') RETURNING ID_PERSON INTO vIdPerson</p>\n</blockquote>\n\n<p>or if you are calling from... Java with a CallableStatement (sry, it's my field)</p>\n\n<blockquote>\n  <p>INSERT INTO PERSON (NAME) VALUES ('JACK') RETURNING ID_PERSON INTO ?</p>\n</blockquote>\n\n<p>and declaring an autput parameter for the statement</p>\n"}, {'answer_id': 45764, 'author': 'Vincent Robert', 'author_id': 268, 'author_profile': 'https://Stackoverflow.com/users/268', 'pm_score': 2, 'selected': False, 'text': '<p>An important note is that using vendor SQL queries to retrieve the last inserted ID are safe to use without fearing about concurrent connections.</p>\n\n<p>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.</p>\n\n<p>But these vendor specific queries always retrieve the last inserted ID <strong>for the current connection to the database</strong>. It means that the last inserted ID cannot be affected by other client insertions as long as they use their own database connection.</p>\n'}, {'answer_id': 45775, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 3, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>CREATE TABLE mytable (\n  id SERIAL PRIMARY KEY,\n  lastname VARCHAR NOT NULL,\n  firstname VARCHAR\n);\n</code></pre>\n\n<p>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):</p>\n\n<pre><code>INSERT INTO mytable (lastname, firstname) VALUES (\'Washington\', \'George\');\nSELECT lastval();\n</code></pre>\n\n<p><em><a href="http://www.postgresql.org/docs/8.3/static/functions-sequence.html" rel="noreferrer">lastval()</a></em> gives you the last auto-generated sequence value used in the current connection.</p>\n\n<p>The other way is to use PostgreSQL\'s <em>RETURNING</em> clause on the <em><a href="http://www.postgresql.org/docs/8.3/static/sql-insert.html" rel="noreferrer">INSERT</a></em> statement:</p>\n\n<pre><code>INSERT INTO mytable (lastname) VALUES (\'Cher\') RETURNING id;\n</code></pre>\n\n<p>This form returns a result set just like a <em>SELECT</em> statement, and is also handy for returning any kind of calculated default value.</p>\n'}, {'answer_id': 45820, 'author': 'NotMe', 'author_id': 2424, 'author_profile': 'https://Stackoverflow.com/users/2424', 'pm_score': 2, 'selected': False, 'text': "<p>For SQL 2005:</p>\n\n<p>Assuming the following table definition:</p>\n\n<pre><code>CREATE TABLE [dbo].[Test](\n    [ID] [int] IDENTITY(1,1) NOT NULL,\n    [somevalue] [nchar](10) NULL,\n) \n</code></pre>\n\n<p>You can use the following:</p>\n\n<pre><code>INSERT INTO Test(somevalue)\nOUTPUT INSERTED.ID\nVALUES('asdfasdf')\n</code></pre>\n\n<p>Which will return the value of the ID column.</p>\n"}, {'answer_id': 45949, 'author': 'Wyck Hebert', 'author_id': 4772, 'author_profile': 'https://Stackoverflow.com/users/4772', 'pm_score': 2, 'selected': False, 'text': "<p>This is how I've done it using parameterized commands.</p>\n\n<p>MSSQL</p>\n\n<pre><code> INSERT INTO MyTable (Field1, Field2) VALUES (@Value1, @Value2); \n SELECT SCOPE_IDENTITY(); \n</code></pre>\n\n<p>MySQL</p>\n\n<pre><code> INSERT INTO MyTable (Field1, Field2) VALUES (?Value1, ?Value2);\n SELECT LAST_INSERT_ID();\n</code></pre>\n"}, {'answer_id': 67540, 'author': 'dummy', 'author_id': 6297, 'author_profile': 'https://Stackoverflow.com/users/6297', 'pm_score': 0, 'selected': False, 'text': '<ol>\n<li>insert the row with a known guid. </li>\n<li>fetch the autoId-field with this guid. </li>\n</ol>\n\n<p>This should work with any kind of database. </p>\n'}, {'answer_id': 312905, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>An Environment Based Oracle Solution:</p>\n\n<pre><code>CREATE OR REPLACE PACKAGE LAST\nAS\nID NUMBER;\nFUNCTION IDENT RETURN NUMBER;\nEND;\n/\n\nCREATE OR REPLACE PACKAGE BODY LAST\nAS\nFUNCTION IDENT RETURN NUMBER IS\n    BEGIN\n    RETURN ID;\n    END;\nEND;\n/\n\n\nCREATE TABLE Test (\n       TestID            INTEGER ,\n    Field1      int,\n    Field2      int\n)\n\n\nCREATE SEQUENCE Test_seq\n/\nCREATE OR REPLACE TRIGGER Test_itrig\nBEFORE INSERT ON Test\nFOR EACH ROW\nDECLARE\nseq_val number;\nBEGIN\nIF :new.TestID IS NULL THEN\n    SELECT Test_seq.nextval INTO seq_val FROM DUAL;\n    :new.TestID := seq_val;\n    Last.ID := seq_val;\nEND IF;\nEND;\n/\n\nTo get next identity value:\nSELECT LAST.IDENT FROM DUAL\n</code></pre>\n'}, {'answer_id': 312909, 'author': 'Daniel Schaffer', 'author_id': 2596, 'author_profile': 'https://Stackoverflow.com/users/2596', 'pm_score': 4, 'selected': False, 'text': '<p>This works very nicely in SQL 2005:</p>\n\n<pre><code>DECLARE @inserted_ids TABLE ([id] INT);\n\nINSERT INTO [dbo].[some_table] ([col1],[col2],[col3],[col4],[col5],[col6])\nOUTPUT INSERTED.[id] INTO @inserted_ids\nVALUES (@col1,@col2,@col3,@col4,@col5,@col6)\n</code></pre>\n\n<p>It has the benefit of returning all the IDs if your INSERT statement inserts multiple rows.</p>\n'}, {'answer_id': 3159308, 'author': 'James Lawruk', 'author_id': 88204, 'author_profile': 'https://Stackoverflow.com/users/88204', 'pm_score': 2, 'selected': False, 'text': '<pre><code>sql = "INSERT INTO MyTable (Name) VALUES (@Name);" +\n      "SELECT CAST(scope_identity() AS int)";\nSqlCommand cmd = new SqlCommand(sql, conn);\nint newId = (int)cmd.ExecuteScalar();\n</code></pre>\n'}, {'answer_id': 3837093, 'author': 'DBinukumar', 'author_id': 463593, 'author_profile': 'https://Stackoverflow.com/users/463593', 'pm_score': 2, 'selected': False, 'text': '<p>From the site i found out the following things:</p>\n\n<p><a href="http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/" rel="nofollow">SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record</a>\nMarch 25, 2007 by pinaldave </p>\n\n<p>SELECT @@IDENTITY\nIt returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.\n@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.</p>\n\n<p>SELECT SCOPE_IDENTITY()\nIt returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.\nSCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.</p>\n\n<p>SELECT IDENT_CURRENT(‘tablename’)\nIt returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.\nIDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. </p>\n'}, {'answer_id': 12000913, 'author': 'Jan Remunda', 'author_id': 77154, 'author_profile': 'https://Stackoverflow.com/users/77154', 'pm_score': 2, 'selected': False, 'text': '<p>Ms SQL Server: this is good solution even if you inserting more rows:</p>\n\n<pre><code> Declare @tblInsertedId table (Id int not null)\n\n INSERT INTO Test ([Title], [Text])\n OUTPUT inserted.Id INTO @tblInsertedId (Id)\n SELECT [Title], [Text] FROM AnotherTable\n\n select Id from @tblInsertedId \n</code></pre>\n'}, {'answer_id': 16434253, 'author': 'SuperLucky', 'author_id': 766963, 'author_profile': 'https://Stackoverflow.com/users/766963', 'pm_score': 0, 'selected': False, 'text': '<p>In TransactSQL, you can use OUTPUT clause to achieve that.</p>\n\n<pre><code>INSERT INTO my_table(col1,col2,col3) OUTPUT INSERTED.id VALUES(\'col1Value\',\'col2Value\',\'col3Value\')\n</code></pre>\n\n<p>FRI: <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms177564.aspx</a></p>\n'}, {'answer_id': 24127021, 'author': 'LiranBo', 'author_id': 3022900, 'author_profile': 'https://Stackoverflow.com/users/3022900', 'pm_score': 0, 'selected': False, 'text': '<p>Simplest answer:</p>\n\n<pre><code>command.ExecuteScalar()\n</code></pre>\n\n<p>by default returns the first column</p>\n\n<p>Return Value\nType: System.Object\nThe first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.</p>\n\n<p>Copied from <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx" rel="nofollow">MSDN</a></p>\n'}]
['https://Stackoverflow.com/questions/45651', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/184/']