locked
Generate autonumber RRS feed

  • Question

  • Dear All,

    Plz help me to generate autoNumber.

    Ways to Generate AutoNumber

    1. Identity------ But Here problem is there it srno using in another table as ref. so when I will import data then I will loose the actual link data.

    2. Having a table which store a Max value only. For ex.

      Update tblEmpIDMax set MaxID = MaxID + 1

    then
              1.          select @pMaxID  = MaxID from  tblEmpIDMax
              2.          insert into tblEmp values ( @pMaxID  ,,,,,,)


               But Problem is of deadlock and primary key violation In case of concorrent Hits.
    3.  By Selecting MaxID + 1 ----- this one also created primary key violation In case of concorrent Hits.


    So plz plz any one can help me out to have best way to generate autonumbers which will not create any deadlock and primary key violatons.

    Thanks,



    jayesh pavar
    Saturday, January 31, 2009 11:11 AM

Answers

All replies

  • Look at this thread and the answer of Bodo Michael http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/cc3f6558-f349-4ac1-9e1e-02e45c2b1785

    I think the above thread will help you to solve this.

    http://jacobsebastian.blogspot.com/
    Saturday, January 31, 2009 1:03 PM
  • The most 'robust' solution is to use an IDENTITY value, and then either SCOPE_IDENTITY, or the OUTPUT clause with the first table insert.
    Then have the INSERT into the first table return the new value to the code so that it can be used with subsequent inserts to other tables.

    Here is an example of how to INSERT data into TWO (2) tables with a single INSERT statement using the OUTPUT clause.

    --*************************************************************************  
    -- Problem: Insert Data into two tables with a single INSERT statement  
    --          Uses OUTPUT  
    --*************************************************************************  
     
    SET NOCOUNT ON 
     
    DECLARE @Table1 table 
       (  RowId   int  IDENTITY(101,1),  
          t1Value varchar(20)  
       )  
     
    DECLARE @Table2 table 
       (  RowId   int,  
          t2Value varchar(20),  
          t2Date  date 
       )  
     
    INSERT INTO @Table1  
          OUTPUT 
             inserted.RowId,  
             inserted.t1Value,  
             getdate()  
          INTO @Table2  
       VALUES 
          ( 'Table1Insert' )  
     
    SELECT   
       RowID,  
       t1Value  
    FROM @Table1  
     
    /*  
    RowID   t1Value  
    101     Table1Insert  
    */  
     
    SELECT   
       RowID,  
       t2Value,  
       t2Date  
    FROM @Table2  
     
    /*  
    RowID   t2Value         t2Date  
    101     Table1Insert    2009-01-27  
    */  
     

    You may be only one person in the world, but you may also be the world to one person.
    • Proposed as answer by Aaron Alton Saturday, January 31, 2009 10:57 PM
    Saturday, January 31, 2009 4:37 PM
  •  See if this helps.

    Deadlocks with Custom Sequence
    http://www.sqlmag.com/Articles/ArticleID/95538/95538.html?Ad=1


    AMB
    • Marked as answer by jayesh pavar Monday, February 2, 2009 5:59 AM
    Saturday, January 31, 2009 7:03 PM
  • Hunchback said:

     See if this helps.

    Deadlocks with Custom Sequence
    http://www.sqlmag.com/Articles/ArticleID/95538/95538.html?Ad=1


    AMB


    Thanks a lot Dear for your co-operaiton. 10000 hits worked out within Avg 20 secs without any primary key violation and DeadLock.

    jayesh pavar
    • Proposed as answer by gveeEditor Monday, February 2, 2009 12:41 PM
    • Unproposed as answer by gveeEditor Monday, February 2, 2009 12:41 PM
    Monday, February 2, 2009 6:53 AM