UPDATING IDENTITY COLUMN

• mdouglass

  Default port

  Points: 1412

  More actions

  September 20, 2006 at 8:33 am

  #170429

  Hi,

  I have a very small lookup table that is linked to a larger table.

  In this small table it has an index auto number (identity column) with values

  1

  2

  3

  4

  5

  6

  10

  The 10 value I want to reset to a value of 8! (Result of deleting some entries)

  Is it possibly using T-SQL to reset the value of this identity column from 10 to 8?

  Then RESET the seed back to 9 using DBCC CHECKIDENT('table_name', RESEED, 9)

  Thanks.

  Mike

  

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 20, 2006 at 8:35 am

  #661512

  There's a way to do it but why do you want to do it?  Do you have an obligation to have that number in sequential order or you just think it's prettier (which it is )?

• mdouglass

  Default port

  Points: 1412

  More actions

  September 20, 2006 at 8:43 am

  #661518

  Hi,

  The value is important as it needs to match a database in the States I'm mirroring!

  I have sorted it...

  I deleted the 10 entry and then executed the following code:

  SET IDENTITY_INSERT tblTasksType ON

  INSERT tblTasksType (type_id, name, hidden, image, bold) VALUES (8, 'Opportunity Follow-up', 0, 'icon_opportunity.gif',0)

  SET IDENTITY_INSERT tblTasksType OFF

  DBCC CHECKIDENT('tblTasksType', RESEED, 9)

  Thanks for your reply...

  Mike