locked
simple insert and retrive last ID RRS feed

  • Question

  • hi all
      
    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    select * from dbo.EtlAudit
     
    auditID  etltype  filenme
    1	test1    TestFile1_20110826.txt
    2	test2    TestFile2_20110826.txt
    3	test3    TestFile3_20110826.txt
    4	test    TestFile4_20110826.txt
    
    
    
    
    
    
    

    i have insert 1 record, and select * gave me all the records in table etlaudit. no here is it, i wan to get the latest records inserted. i want to know how to user @@rowcount. suggest please
    Friday, August 26, 2011 5:12 PM

Answers

  • Added to Kent's provided answer you may use @@Identity and Ident_Current as well.

    Identity

    Ident_Current

    • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

    • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

    • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:22 PM

  • --OUTPUT clause is the best. (SQL 2005+)
    declare @Output table (ID int) insert into myTable (...) values (...) OUTPUT Inserted.ID into Output select * from Output

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


    My blog

    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:35 PM
  • Give a look at SCOPE_IDENTITY in books online.  Perhaps try something like

    declare @last_Id integer
    
    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    set @last_Id = scope_Identity

     

    • Proposed as answer by Arbi Baghdanian Friday, August 26, 2011 5:18 PM
    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:16 PM

All replies

  • Give a look at SCOPE_IDENTITY in books online.  Perhaps try something like

    declare @last_Id integer
    
    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    set @last_Id = scope_Identity

     

    • Proposed as answer by Arbi Baghdanian Friday, August 26, 2011 5:18 PM
    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:16 PM
  • Added to Kent's provided answer you may use @@Identity and Ident_Current as well.

    Identity

    Ident_Current

    • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

    • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

    • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:22 PM
  • thanks, but where is @@row count used?
    Friday, August 26, 2011 5:22 PM
  • If you want to know the latest ID inserted to a table, you will use provided functions. Could you please clear it why you want to @@RowCount?

    If you want to know how many rows your table have after any Insert,Update,Delete, or SELECT statement you can use

    Select @@RowCount.

    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    select * from dbo.EtlAudit
     
    auditID etltype filenme
    1	test1 TestFile1_20110826.txt
    2	test2 TestFile2_20110826.txt
    3	test3 TestFile3_20110826.txt
    4	test TestFile4_20110826.txt
    
    Select Scope_Identity() As [ScopeIdentity], @@RowCount As [RowCount]
    

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered

    Friday, August 26, 2011 5:25 PM
  • thanks for the reply Arbi, i am just that curious to know what is @@rowcount.

    also, there can be multiple insertions happening on elaudit table from serveral location/sessions/scops, so which is best among to use identity_current, @identity, scope_identity

    Friday, August 26, 2011 5:33 PM

  • --OUTPUT clause is the best. (SQL 2005+)
    declare @Output table (ID int) insert into myTable (...) values (...) OUTPUT Inserted.ID into Output select * from Output

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


    My blog

    • Marked as answer by Dkuud Friday, August 26, 2011 5:44 PM
    Friday, August 26, 2011 5:35 PM
  • Regarding @@RowCount, it will be same as you do SELECT Count(*).

    For Inserting, I will go with SCOPE_IDENTITY as Kent provided.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Friday, August 26, 2011 5:36 PM
  • Some of the use of @@Rowcount is at this time historical and mostly archaic.  @@Rowcount returns the number of rows processed by the previous SQL command executed.  There are times that you will want to use that information.  Before we had the Try / Catch syntax @@rowcount might be used to trap for errors -- that is like in your case to verify that the row was actually inserted.  Such as:

    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    if @@rowcount <> 1
    begin
     (some error code)
    end
    
    

     

    But as I said, this is now archaic.  Rather than use @@rowcount for error trapping use TRY and CATCH syntax.
    Friday, August 26, 2011 5:44 PM
  • Arbi,

    The @@ROWCOUNT is re-set after each statement. So, say, you inserted one record into the table, @@ROWCOUNT will be 1. You updated 2 records in a table with one statement, @@ROWCOUNT will be 2.

    In other words, @@ROWCOUNT shows number of records affected by the last run statement.

    OUTPUT clause is the preferred method of getting the identity value back over SCOPE_IDENTITY() (second best). I read in this forum a few years back about some weird issues with SCOPE_IDENTITY() in the high concurrency scenario.


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


    My blog
    Friday, August 26, 2011 5:45 PM
  • Thanks Naomi,

    I will keep in mind that and thanks for correction.

    INSERT INTO dbo.EtlAudit	(EtlType ,FileNme)
    values ('test', 'TestFile_20110826.txt')
    
    select * from dbo.EtlAudit
    Select @@RowCount As [CountRows] --Return Count(*)
    
    auditID etltype filenme
    1	test1 TestFile1_20110826.txt
    2	test2 TestFile2_20110826.txt
    3	test3 TestFile3_20110826.txt
    4	test TestFile4_20110826.txt
    
    Select Scope_Identity() As [ScopeIdentity]
    


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Friday, August 26, 2011 5:50 PM