Post reply

DELETE vs TRUNCATE on table with IDENTITY col

  • July 17, 2009 at 9:53 am

    #212325

    Is there a way to utilize the TRUNCATE statement on a table containing a PK IDENTITY column and also PRESERVE the IDENTITY value?

    For example…On a given table, I observe the IDENTITY values change based on a DELETE vs. TRUNCATE ... IF Table "A" has 10 rows in it and the values in the PK IDENTITY column are 112, 113, 114 ... 121:

    A DELETE statement preserves the IDENTITY value.. And subsequent LOADs (INSERTS, etc) into this table would begin w/ PK value = 122, 123, 124.. Etc.

    A TRUNCATE statement resets the IDENTITY back to 0.. And subsequent LOADs (INSERTS, etc) into this table would begin w/ PK value = 1, 2, 3.. Etc.

    BT

  • July 17, 2009 at 10:06 am

    #1026296

    Automagically no, but you could capture the highest value in a variable and then reset the table after..

    DECLARE @maxid int

    SELECT @maxid = MAX(Field1) FROM dbo.sometable

    TRUNCATE TABLE dbo.sometable

    DBCC CHECKIDENT( "dbo.sometable", RESEED, @maxid )

    Clear?

    CEWII

  • July 17, 2009 at 10:43 am

    #1026316

    cool suggestion.. cool beans.. thank you!

    BT

Viewing 3 posts - 1 through 2 (of 2 total)

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