<p>In SQL Server 2005 I have an "id" field in a table that has the "Is Identity" property set to 'Yes'. So, when an Insert is executed on that table the "id" gets set automatically to the next incrementing integer. Is there an easy way when the Insert is executed to get what the "id" was set to without having to do a Select statement right after the Insert?</p>

<blockquote>
  <p>duplicate of:<br>
  <a href="https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row">Best way to get identity of inserted row?</a></p>
</blockquote>

[{'answer_id': 55164, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 1, 'selected': False, 'text': '<p>You have to select the scope_identity() function.  </p>\n\n<p>To do this from application code, I normally encapsulate this process in a stored procedure so it still looks like one query to my application.</p>\n'}, {'answer_id': 55170, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': '<p>Scope_identity() is the preferred way, see: <a href="http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value" rel="nofollow noreferrer">6 Different Ways To Get The Current Identity Value</a></p>\n'}, {'answer_id': 55172, 'author': 'Josh Hinman', 'author_id': 2527, 'author_profile': 'https://Stackoverflow.com/users/2527', 'pm_score': 6, 'selected': True, 'text': '<p>In .Net at least, you can send multiple queries to the server in one go. I do this in my app:</p>\n\n<pre><code>command.CommandText = "INSERT INTO [Employee] (Name) VALUES (@Name); SELECT SCOPE_IDENTITY()";\nint id = (int)command.ExecuteScalar();\n</code></pre>\n\n<p>Works like a charm.</p>\n'}, {'answer_id': 55190, 'author': 'David Basarab', 'author_id': 2469, 'author_profile': 'https://Stackoverflow.com/users/2469', 'pm_score': 2, 'selected': False, 'text': '<p>SCOPE_IDENTITY(); is your best bet.  And if you are using .NET just pass an our parameter and check the value after the procedure is run.</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': 55299, 'author': 'kamajo', 'author_id': 5415, 'author_profile': 'https://Stackoverflow.com/users/5415', 'pm_score': 4, 'selected': False, 'text': "<p>If you're inserting multiple rows, the use of the <code>OUTPUT</code> and <code>INSERTED.columnname</code> clause  on the <code>insert</code> statement is a simple way of getting all the ids into a temp table. </p>\n\n<pre><code>DECLARE @MyTableVar table( ID int,  \n                               Name varchar(50),  \n                               ModifiedDate datetime);  \nINSERT MyTable  \n        OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar  \nSELECT someName, GetDate() from SomeTable  \n</code></pre>\n"}, {'answer_id': 55329, 'author': 'callingshotgun', 'author_id': 2876, 'author_profile': 'https://Stackoverflow.com/users/2876', 'pm_score': 1, 'selected': False, 'text': '<p>I tend to prefer attaching a trigger to the table using enterprise manager.  That way you don\'t need to worry about writing out extra sql statements in your code.  Mine look something like this:</p>\n\n<p>Create Trigger tblName\nOn dbo.tblName\nFor Insert\nAs\nselect new_id = @@IDENTITY</p>\n\n<p>Then, from within your code, treat your insert statements like select statements-  Just execute and evaluate the results.  the "newID" column will contain the identity of the row you just created.</p>\n'}, {'answer_id': 27740695, 'author': 'Udit Narayan', 'author_id': 2267783, 'author_profile': 'https://Stackoverflow.com/users/2267783', 'pm_score': 0, 'selected': False, 'text': '<p>This is probably the best working solution I found for SQL Server..\n<a href="https://stackoverflow.com/questions/17993606/sql-server-return-the-value-of-identity-column-after-insert-statement">Sql Server return the value of identity column after insert statement</a></p>\n'}]
['https://Stackoverflow.com/questions/55159', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1096640/']