1

In a nutshell, I have a temp table which stores a range of data. The number of rows could be dynamic depending on when this query is run. I'm then trying to alter the table and add an identity which I want to start at a defined value that I have chosen. Whatever I seem to have tried, when viewing the data the identity column always starts at 1…and increments and not the value I specified. 

--At this point of the code the data is already in the table!
Set existingNumber =
(
--Get a number from a table
)

Set existingNumber = existingNumber + 1
Alter table #myTable
Add testID bigINT Identity
DBCC CHECKIDENT (#myTable, RESEED, @existingNumber)

Is there a way that I can simply restart the Identity column for pre existing values?

I have tried looking at creating the identity with the table but this causes another problem due to how our company framework is unfortunately.

EDIT:

Using SQL Management Studio

Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
Aiden
  • 179
  • 2
  • 13
  • @RyanWilson I've updated my question to put in SQLMS – Aiden Oct 23 '18 at 16:02
  • I added the sql-server tag for you, and thank you. – Ryan Wilson Oct 23 '18 at 16:03
  • you may want to look at one of the answers on this post (https://stackoverflow.com/questions/1280705/set-start-value-for-column-with-autoincrement) the one with 9 upvotes, explains how you can use `SET IDENTITY_INSERT` to ON and then you can insert your own identity values and then turn it back off to allow for the next insert to begin with your last identity value. – Ryan Wilson Oct 23 '18 at 16:05
  • I have tested similar code to yours and had no issue. Was able add an identity column to a temp table, RESEED that using DBCC CHECKIDENT, passing in the reseed value as a variable. Worked fine. – Tim Mylott Oct 23 '18 at 16:18

1 Answers1

0

Your @existingNumber variable is not being set correctly. The below example works fine..

declare @existingNumber int = 1000
create table #test (col1 varchar(20))
alter table #test add testId bigint Identity

dbcc checkident (#test, reseed, @existingNumber)

insert into #test values ('test record')

select * from #test

drop table #test
Cookie Monster
  • 475
  • 6
  • 12
  • I haven't been able to test this theory yet due to an unexpected Windows Update! Does this also work if the data for the table is populated first? So the insert statement before and after the dbcc? When I had tested earlier, any new data after the dbcc command would have the correct value, but the old data would still have the wrong values. – Aiden Oct 23 '18 at 18:37
  • @Aiden no, https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017, "cannot use DBCC CHECKIDENT to perform a change to the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value." – Tim Mylott Oct 23 '18 at 19:33
  • so basically any existing rows will retain their original value, any new rows after changing would reflect the new seed value moving forward, unless, as noted, you drop and recreate the column. – Tim Mylott Oct 23 '18 at 19:36
  • Right - so that's where my problem is I believe. In my question, I've tried adding in a new identity column on a temp table with populated data yet it starts with 1 on the existing data. – Aiden Oct 23 '18 at 19:57
  • You need to specify the seed value if you don't want it to start from 1 for any existing data. 'Add testID bigINT Identity(1000,1)' will start the identity value at 1000 and then increment it by 1 – Cookie Monster Oct 24 '18 at 08:56
  • Thanks - I did have a look at that by a SQLMS bug prevent you passing in variables as the seed. I've built a dynamic query which has done the trick! – Aiden Oct 24 '18 at 10:11