none
how to return an auto increment table value after insert query

    Question

  • HI all,

    how can i know what the latast auto increment value is after running an insert query from VB.net.

    for example i have two tables:

    TABLE 1

    (invoice no)  (transaction date)
    1                   2009-01-01
    2                   2009-01-02
    3                   2009-01-02
    ------------------------------

    TABLE 2

    (invoice no)   (transaction items)
    1                  apple
    1                  banana
    2                  oranges
    2                  lemons
    ------------------------


    a  transaction is captured with a VB.net GUI.
    VB sends the date to TABLE 1 and the invoice number is created by auto increment.
    how can i know what value was given to the invoice in TABLE 1 so that TABLE 2 can use this value as reference? I need this number returned to VB so i can run the insert query for TABLE 2

    Thanks in advance for your assistance
    Sunday, January 31, 2010 7:34 AM

Answers

  • hi,
    SCOPE_IDENTITY returns the info about your current session, and should be the one for you..

    BTW, I'd not use an idenity based column for my "invoices" cardinality.. the invoice numbering should usually be without gaps, but if your INSERT ... statement fails for some reason, you'll end up with holes (gaps) in your invoice numbering..
    please see the following sample..

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
     Id int IDENTITY PRIMARY KEY,
     Data int CHECK (Data > 10)
     );
    GO
    INSERT INTO dbo.t VALUES ( 5 );
    GO
    INSERT INTO dbo.t VALUES ( 15 );
    GO
    SELECT * from dbo.t;
    GO
    DROP TABLE dbo.t;
    --<-----------
    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the CHECK constraint "CK__t__Data__239E4DCF".
     The conflict occurred in database "tempdb", table "dbo.t", column 'Data'.
    The statement has been terminated.
    Id          Data
    ----------- -----------
    2           15

    suppose you have a constraint that requires your "Data" (int) column to have values greater than 10..
    if you try to insert a row with a value less than that, your insert will fail, but the internal identity value gets used and consumed.. the next insertion will get another value than the one you expect..

    going further, invoices numbers are often not supposed to be expressed in the integer domain [at least here in Italy :) ].. they can contain alfabetic letters as well, even sometime expressed as "CustomerCodeNum/Invoice Num" meaning "I015-E34/000001" or the like..

    another problem will come "next year"... you'll probably need to restart your invoice number from 1.. with an identiy based column, you have to reseed the identity column, http://msdn.microsoft.com/en-us/library/ms176057.aspx, and appropriate permissions are required to do that, permissions that are not usually granted to the "traditional interactive user"..
    this is not a problem about the uniqueness of the value, as the IDENTITY property does absolutely not provide this warrant and an appropriate unique constraint must be defined, but about the appropriate definition of the domain and the "rules" that constraint the data..
    my $0.02

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Tuesday, February 02, 2010 12:20 AM
    Moderator
  • Hello juan_D,

    there are two ways: You can use the function @@ Identity or (better) since version 2005 the output clause:


    CREATE TABLE #tmp (id int identity(100, 1));

    INSERT INTO #tmp DEFAULT VALUES;

     

    SELECT @@IDENTITY

    GO

    CREATE TABLE #result (id int);

     

    INSERT INTO #tmp

    OUTPUT inserted.id

    DEFAULT VALUES

    GO

     

    DROP TABLE #tmp;

    DROP TABLE #result;

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by LekssEditor Sunday, January 31, 2010 9:37 PM
    • Unproposed as answer by juan_D Monday, February 01, 2010 3:16 PM
    • Marked as answer by Andrea MontanariModerator Monday, February 08, 2010 1:06 AM
    Sunday, January 31, 2010 9:47 AM

All replies

  • Hello juan_D,

    there are two ways: You can use the function @@ Identity or (better) since version 2005 the output clause:


    CREATE TABLE #tmp (id int identity(100, 1));

    INSERT INTO #tmp DEFAULT VALUES;

     

    SELECT @@IDENTITY

    GO

    CREATE TABLE #result (id int);

     

    INSERT INTO #tmp

    OUTPUT inserted.id

    DEFAULT VALUES

    GO

     

    DROP TABLE #tmp;

    DROP TABLE #result;

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by LekssEditor Sunday, January 31, 2010 9:37 PM
    • Unproposed as answer by juan_D Monday, February 01, 2010 3:16 PM
    • Marked as answer by Andrea MontanariModerator Monday, February 08, 2010 1:06 AM
    Sunday, January 31, 2010 9:47 AM
  • Thank you for the advice Olaf!

    This is perhaps a dumb question:

    How do i know the correct IDENTITY is returned.

    The system has mulitiple user updating Table 1, how do i know if the identity returned returns the relevant IDENTITY and not merely the last queery?

    i have read up on: @@IDENTITY , SCOPE_IDENTITY(), IDENT_CURRENT(‘tablename’)

    Now i am even more confused than before

    Thanks in advance for you assistance

    Kind regards
    Monday, February 01, 2010 3:16 PM
  • hi,
    SCOPE_IDENTITY returns the info about your current session, and should be the one for you..

    BTW, I'd not use an idenity based column for my "invoices" cardinality.. the invoice numbering should usually be without gaps, but if your INSERT ... statement fails for some reason, you'll end up with holes (gaps) in your invoice numbering..
    please see the following sample..

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
     Id int IDENTITY PRIMARY KEY,
     Data int CHECK (Data > 10)
     );
    GO
    INSERT INTO dbo.t VALUES ( 5 );
    GO
    INSERT INTO dbo.t VALUES ( 15 );
    GO
    SELECT * from dbo.t;
    GO
    DROP TABLE dbo.t;
    --<-----------
    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the CHECK constraint "CK__t__Data__239E4DCF".
     The conflict occurred in database "tempdb", table "dbo.t", column 'Data'.
    The statement has been terminated.
    Id          Data
    ----------- -----------
    2           15

    suppose you have a constraint that requires your "Data" (int) column to have values greater than 10..
    if you try to insert a row with a value less than that, your insert will fail, but the internal identity value gets used and consumed.. the next insertion will get another value than the one you expect..

    going further, invoices numbers are often not supposed to be expressed in the integer domain [at least here in Italy :) ].. they can contain alfabetic letters as well, even sometime expressed as "CustomerCodeNum/Invoice Num" meaning "I015-E34/000001" or the like..

    another problem will come "next year"... you'll probably need to restart your invoice number from 1.. with an identiy based column, you have to reseed the identity column, http://msdn.microsoft.com/en-us/library/ms176057.aspx, and appropriate permissions are required to do that, permissions that are not usually granted to the "traditional interactive user"..
    this is not a problem about the uniqueness of the value, as the IDENTITY property does absolutely not provide this warrant and an appropriate unique constraint must be defined, but about the appropriate definition of the domain and the "rules" that constraint the data..
    my $0.02

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Tuesday, February 02, 2010 12:20 AM
    Moderator
  • Thank you very much ANDREA!!!

    You have raised a scary point.

    1. I am not concerned with the format of the invoice numbers or it being reset later.

    2. I am however alarmed about the fact that if the insert fails the " internal identity value gets used and consumed.. the next insertion will get another value than the one you expect.."

    This could turn my entire referential database into nonsense. Would it be possible to catch insert errors and prevent the ID from being consumed if the error was caught?

    Kind regards
    Tuesday, February 02, 2010 10:58 AM
  • hi,

    1. I am not concerned with the format of the invoice numbers or it being reset later.

    that's too bad :( :)

    2. I am however alarmed about the fact that if the insert fails the " internal identity value gets used and consumed.. the next insertion will get another value than the one you expect.."

    This could turn my entire referential database into nonsense. Would it be possible to catch insert errors and prevent the ID from being consumed if the error was caught?

    no, you can not trap that, as the IDENTITY value is generated in the same contest of the insertion try.. if it fails, the generation has been already issued and you can not roll it back..
    this is the reason you should not rely on such an atomagic generated value for an attribute that "makes some sense".. self attribute generation pattern is usually ok for indeterminated key values where the key value is not directly related to the "reality" (the data model) you are interested with.. if it makes no sense to you that your CustomerId (a column, an attribute of your "object") has a value of 1 or a value of 100000000, then it's ok to use such a tool like the one we are inspecting.. but in the case of "invoices" then usually this is not ok as a sequential pattern is, again usually, required..
    you can usually solve that in 2 ways.. you get the "MAX(col) + 1" of the actual base table and use that, or you can rely on a auxiliary table.. both solutions are full of caveats, usually related to locks problems..
    1#: you query your table like
    DECLARE @nextID int;
    SELECT @nextId = ISNULL(MAX(i.Id), 0) + 1
        FROM dbo.Invoices AS i
        WHERE i.FiscalYear = 2010;
    -- and consume that value for next invoice INSERTion
    INSERT INTO dbo.Invoices (Id, FiscalYear, InvoiceDate, CustomerId, ....)
        VALUES (@nextId, 2010, GETDATE(), 5, .... );
    -- invoice details, related to the invoice header
    INSERT INTO dbo.InvoiceDetailRow VALUES (Id, FiscalYear, ProductId, Quantity, .... )
        VALUES (@nextId, 2010, 11111, 10, ....);

    if the insertion fails, the next time you try to insert the "MAX(Id) + 1" request will not be influenced by the fail, as it recalculates the value..

    2#: you have an auxiliary table where you store your "current" values for your entity that require progression like
    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.ProgressiveData (
        DataType varchar(20) NOT NULL PRIMARY KEY,
        CurrentId int NOT NULL DEFAULT 0
        );
    INSERT INTO dbo.ProgressiveData VALUES ('Invoice', 0);
    INSERT INTO dbo.ProgressiveData VALUES ('Product', 0);
    INSERT INTO dbo.ProgressiveData VALUES ('Customer', 0);
    GO
    DECLARE @nextId int;
    PRINT 'get next invoice id';

    UPDATE dbo.ProgressiveData
     SET @nextId = CurrentId = CurrentId + 1
     WHERE DataType = 'Invoice';

    PRINT 'consume that value as required';
    PRINT 'INSERT INTO dbo.Invoices (Id, FiscalYear, InvoiceDate, CustomerId, ....)'
    PRINT '    VALUES (' + CONVERT(varchar(10), @nextId) + ', 2010, GETDATE(), 5, .... );'
    PRINT 'etc...';
    GO
    DROP TABLE dbo.ProgressiveData;

    your dbo.ProgressiveData will hold all the progressive "stamps" you require, and you just have to "add 1" to the correct row to get the NextID to consume for invoices/customers/etc.. insertions..
    you will of course put the whole command into an explicit transaction.. if it fails for whatever reason, say as indicated in the other post, you can roll back the whole transaction and the ProgressiveData value will not be affected by the insertion try, as the rollback affects it's changes as well..

    the INSERTion statement must be performed as quickly as possibile as it locks the involved tables (the MAX +1 pattern is affected as well) enqueying successive DML statements on them..

    personally I do prefer method 2#, as it grants you the way to "view" the current state (and value) of your domain, be it invoices, customers, ...., as well as manually manages "anomalies" or "year reseeding" the values...
    but, again, both methods are affected (and must of course be) by locks problems, if not correctly and quickly performed..
    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Wednesday, February 03, 2010 12:20 AM
    Moderator