Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In SQL Server is it possible to get "id" of a record when Insert is executed?

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?

duplicate of:
Best way to get identity of inserted row?

like image 605
Keith Maurino Avatar asked Sep 10 '08 19:09

Keith Maurino


People also ask

How can I get Auto_increment value after insert in SQL Server?

To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.

How do I find the last inserted record id?

If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL.

How to get id of newly inserted record in SQL Server?

Here there is no constraint of scope and session It will give you the ID of the latest record for that table. This is a very frequent question on forums, how to get ID of the newly inserted record. So here it is. It can be simply achieved with the SCOPE_IDENTITY () in SQL Server which stores the ID of the newly inserted record.

When an insert is executed on a table it gets set automatically?

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?

How many records can I insert in a SQL query?

Note, that if the ID column was not a Primary Key we would be able to insert duplicate records. When you SET INDENTITY_INSERT ON it will stay on for the entire session (the time the query window is open). So once this is set you can insert as many records as you want.

How to set the identit_insert flag on in SQL Server?

To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table. Now if we again try to insert the record of the student “Nick” with Id 6, no error will be thrown. Execute the following statement again: You can see that we have specified the name of the columns as well for inserting a record.


2 Answers

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.

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();
like image 102
David Basarab Avatar answered Oct 22 '22 20:10

David Basarab


If you're inserting multiple rows, the use of the OUTPUT and INSERTED.columnname clause on the insert statement is a simple way of getting all the ids into a temp table.

DECLARE @MyTableVar table( ID int,  
                               Name varchar(50),  
                               ModifiedDate datetime);  
INSERT MyTable  
        OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar  
SELECT someName, GetDate() from SomeTable  
like image 34
kamajo Avatar answered Oct 22 '22 19:10

kamajo