none
How to get the auto increment column after doing a SQL Insert?

    Question

  • I have a SQL table that has an auto inc column.  After doing an insert, is there a way to get the that column back?
    Tuesday, April 01, 2008 1:39 PM

All replies

  •  eqs wrote:
    I have a SQL table that has an auto inc column.  After doing an insert, is there a way to get the that column back?

     

    That autoIncrement column must be your primary key? Right? So if you know the number of records in your table, the seed value and the step you can guess tha value even beforehand. Am I making sense, or perhaps you meant something different? Do you want to know the exact value of the last autoIncrement column field?

     

    You can get the number of records by using a system stored procedure in Sql Server.

    Tuesday, April 01, 2008 1:48 PM
  • If you use a stored procedure you can get the last inserted ID from

     

    scope_identity() function 

     

    which you could output to a SQLCommand

     

    Tuesday, April 01, 2008 2:01 PM
  •  AlexBB wrote:

     eqs wrote:
    I have a SQL table that has an auto inc column.  After doing an insert, is there a way to get the that column back?

     

    That autoIncrement column must be your primary key? Right? So if you know the number of records in your table, the seed value and the step you can guess tha value even beforehand. Am I making sense, or perhaps you meant something different? Do you want to know the exact value of the last autoIncrement column field?

     

    You can get the number of records by using a system stored procedure in Sql Server.



    It is the primary key, but the key is not continuous so I cannot just guess it from number of rows.
    Tuesday, April 01, 2008 2:11 PM
  • SELECT SCOPE_IDENTITY() is what you want to use.  Also, in your DataColumn definition, you want to set the seed and the increment to -1.  Assuming that your column in the database uses IDENTITY(1,1), this guarantees that ADO will never assign a key value that already exists in your database.

     

    When you insert a new row into the database from your DataTable, you must then immediately SELECT SCOPE_IDENTITY() to get its actual key value, and update the column in your DataRow with the correct value.  (If you look at the TableAdapter code that the DataSet Designer generates in the TableAdapter, you'll see it's doing just this.) 

     

    Also, make sure that all of the table's child DataRelations that use this column as a foreign key are set to cascade updates:  that way, when you assign the proper value to the parent row's key, the value will cascade down into the related child rows' foreign key, so that when they get inserted into the database relational integrity is preserved.

    Tuesday, April 01, 2008 6:21 PM
  • It depends, if you insert one record, you can use :

    SELECT SCOPE_IDENTITY()

    but if you insert a lot of records, you can use OUTPUT clause to get all the ID columns:

    CREATE TABLE #result
    (id int,
    col1 varchar(10),
    col2 datetime
    )

    INSERT INTO dbo.table1 (col1, col2)
    OUTPUT inserted.id, inserted.col1, inserted.col2 INTO #result (id, col1, col2)
    SELECT col1, col2 FROM dbo.table2

    If you use SQL server 2012, you really should try the new SEQUENCE GENERATOR:

    CREATE SEQUENCE dbo.SerialNumber AS int
    START WITH 1
    INCREMENT BY 1
    CACHE 50;

    DECLARE @id int = NEXT VALUE FOR dbo.SerialNumber;

    INSERT INTO table1 (id, col1, col2)
    VALUES(@id, 'H.Winther', '2012-10-02');

    Tuesday, October 02, 2012 8:48 AM