Post reply

Primary Key Field - Identity

  • May 19, 2005 at 8:31 am

    #66965

    I would like to create a table that stores a unique id value and several other bits of information and that everytime the tables gets truncated, it does not reuse one of the former id values, but rather moves on to the next. Can this be accomplished with the id field be set a a primary key identity field? Or if the table, is truncated, will the id start back at the same identity seed value as specified in the table properties. Any help would be greatly appreciated.

  • May 19, 2005 at 8:53 am

    #559861

    You could create a stored procedure that would:

    1. get the last identity for the table

    2. drop the table

    3. create the table with the new identity seed value set to the last identity you retrieved in step 1

    I am not sure why you want to truncate the data....I am sure you have your reasons. Any one else with ideas?

  • May 19, 2005 at 9:23 am

    #559875

    Hi

    TRUNCATE TABLE  will reset  identity seed  . But delete will preserve the  identity seed  . if it is a small table you can  Use delete . Else Procedure option  you can use .

    Padmakumar

     

     

  • May 19, 2005 at 9:52 am

    #559898

    First star wars quote found I did 

  • May 19, 2005 at 10:20 am

    #559908

    You might want to look in books online for SET IDENTITY_INSERT.

    This looks like it may allow you to "trick" the system by saving the last identity value just before the truncate and then inserting a row with the saved value and deleting the same row.  Subsequent inserts will be an increment of the value you just inserted/deleted.

    Edit: I didn't see Mark's post.  That looks like a good solution but be aware your stored procedure will need to keep up with the most current definition of your table.

     

     

  • May 19, 2005 at 1:17 pm

    #560016

    Look at "DBCC CHECKIDENT" in BOL.

     

  • May 19, 2005 at 7:09 pm

    #560083

    This works but you must insert a dummy row or insert at least one row of new data.

    HTH MIke

    USE TEST

    DECLARE @NewSeed int

    SELECT @NewSeed = max(employeeid) FROM Employees

    TRUNCATE TABLE Employees

    SET @NewSeed = @NewSeed + 1

    SET IDENTITY_INSERT Employees ON

    --insert a dummy record with the next IDENTITY VALUE

    INSERT INTO Employees(EmployeeID,FirstName) VALUES(@NewSeed,'Dummy')

    /* The new row or dummy row must be set before you set identity_insert tablename to off*/

    SET IDENTITY_INSERT Employees OFF

    INSERT INTO Employees(FirstName)Values('Dummy2')

     

     

  • May 20, 2005 at 8:11 am

    #560209

    After looking at this suggestion from PhilPacha it apprears to be the best solution.  So check out DBCC CHECKIDENT.

     

  • May 24, 2005 at 5:48 am

    #560722

    Thanks all. I used DBCC CHECKIDENT, as suggested by PhilPacha, to look for the last identity value used and then incremented that value. Your feedback was greatly appreciated!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply