none
INSERTING rows and get at the same time the ID number of the row

    Question

  • Hello,

    This may be a silly question but I will ask anyway, hehehe

    I am inserting some rows into a table, this table has the primary key an Integer (ID column) and it is increased automatically.

    INSERT INTO MyTable ([Name], [Department]) VALUES (["John"],["Mechanics"])

    When I insert this, and ID is generated automatically:

    ID         Name    Department

    1234    John      Mechanics

    QUESTION:

    Is it possible to get from the same INSERT query the ID number?


    Kikeman Electric Systems Engineer
    Tuesday, January 03, 2012 12:14 AM

Answers

  • Yes. In SQL Server 2005 and up the simplest method will be in using OUTPUT clause, e.g.

     

    INSERT Into MyTable ([Name], [Department]) OUTPUT Inserted.ID values ('John','Mechanics')
    
    
    You can also check SCOPE_IDENTITY() function in Help.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi NEditor Tuesday, January 03, 2012 2:21 AM
    • Proposed as answer by Tiya01 Tuesday, January 03, 2012 4:18 AM
    • Marked as answer by Kikeman Thursday, February 23, 2012 2:26 PM
    Tuesday, January 03, 2012 12:17 AM
    Answerer

All replies

  • Yes. In SQL Server 2005 and up the simplest method will be in using OUTPUT clause, e.g.

     

    INSERT Into MyTable ([Name], [Department]) OUTPUT Inserted.ID values ('John','Mechanics')
    
    
    You can also check SCOPE_IDENTITY() function in Help.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi NEditor Tuesday, January 03, 2012 2:21 AM
    • Proposed as answer by Tiya01 Tuesday, January 03, 2012 4:18 AM
    • Marked as answer by Kikeman Thursday, February 23, 2012 2:26 PM
    Tuesday, January 03, 2012 12:17 AM
    Answerer
  • Is it save? I mean, there is a lot of people inserting and erasing rows from the table, there may be some other people that would be also inserting rows at the same time to the database, while someone else would be also inserting rows,

    If I use output, am I getting the correct ID from the inserted Row or only the last ID of the last inserted row?


    Kikeman Electric Systems Engineer
    Tuesday, January 03, 2012 12:24 AM
  • If you use OUTPUT and insert more than one record in one statement, using OUTPUT you will get all new IDs. If you insert only one row, then you will get the ID of that inserted row. OUTPUT always work correctly.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, January 03, 2012 12:29 AM
    Answerer
  • Sorry, I am a real beginner, I am getting "syntax error near OUTPUT" and am using:

    SERT INTO MyTable ([Name], [Department]) VALUES (["John"],["Mechanics"]) OUTPUT Inserted.ID INTO @IDInsert

                            SqlParameter param2 = new SqlParameter();
                            param2.Direction = ParameterDirection.Output;
                            param2.ParameterName = "@IDInsert";
                            param2.DbType = DbType.Int32;
                            SQLCommand.Parameters.Add(param2);

    I tried also:

    SERT INTO MyTable ([Name], [Department]) VALUES (["John"],["Mechanics"]) OUTPUT Inserted.ID

    the same error ...


    Kikeman Electric Systems Engineer
    Tuesday, January 03, 2012 12:57 AM
  • BOL gives an example of the syntax

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

    The correct syntax is

    INSERT INTO MyTable ([Name], [Department]) 
    OUTPUT Inserted.ID
    VALUES (["John"],["Mechanics"]) 
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, January 03, 2012 2:25 AM
    Answerer
  • Try this

    INSERT INTO MyTable ([Name], [Department]) OUTPUT Inserted.ID INTO @IDInsert VALUES ('John','Mechanics')
    



    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Tuesday, January 03, 2012 2:34 AM
  • Hi Kikeman,

    If you are talking about insertion in some other table and then you are confused that output will work or not. Than answer is :

    Yes OUTPUT clause will work correctly. As it is associated with the desired table name where as @@identity and @scope_identity wil fail here.

    Hope this helps.


    Every day its a new learning. Keep Learning!!
    If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful
    Tuesday, January 03, 2012 4:35 AM