Changing Identity In Existing Column SQL Server 2000

Question

Post reply

Changing Identity In Existing Column SQL Server 2000

Dawn O'Shaughnessy

SSC Enthusiast

Points: 144
More actions
July 20, 2007 at 12:29 pm

#175423

I would like to re-number all of the entries within my table so that the new number scheme is unique to that table.
I used the following command, but I keep receiving the below error message.
ALTER TABLE DAWNTEST ALTER COLUMN [ID] int IDENTITY(500000,1) NOT NULL
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
Any idea what I am doing wrong? I issued the command via Query Analyzer.

Many Thanks!
Dawn

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Check BOL, DBCC CHECKIDENT.

Looks like this:

DBCC CHECKIDENT('DAWNTEST', RESEED, 500000)

Dawn O'Shaughnessy SSC Enthusiast Points: 144 More actions · Answer 2

This command worked great for any new entry that was added to the table.

However, It did not re-number the 8 entries that I already had in the table.

I was trying to re-number ID #'s 1 thru 8 to 500000 to 500007.

Any other suggestions??

Thanks!

Dawn

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Off hand, no, but that isn't what I thought you were asking for either in your original post. I'll have to look at this a little more.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

It's not elegant but:

create DawnTemp <according to new criteria>
insert into DawnTemp <everything but identity col>
select <everything but identity col> from DawnTest
make sure everything went over all right
drop DawnTest
rename DawnTemp to DawnTest

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Dave McCabe Grasshopper Points: 16 More actions · Answer 5

Or just turn set identity = No for the column, renumber and turn identity = Yes

Dave

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

also you can use the set identity_insert to on to insert values to indentity columns

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

vadba SSChampion Points: 11133 More actions · Answer 7

Another way:

1. Run DBCC CHECKIDENT('DAWNTEST', RESEED, 499999)

2. Insert a second copy of the data into the table

3. Delete the old data from the table.

Here's an example:

CREATE TABLE #DawnTest

(

id int IDENTITY(1,1)

, mydata varchar(20)

)

INSERT #DawnTest (mydata)

SELECT 'Row One'

UNION ALL

SELECT 'Row Two'

UNION ALL

SELECT 'Row Three'

UNION ALL

SELECT 'Row Four'

UNION ALL

SELECT 'Row Five'

UNION ALL

SELECT 'Row Six'

UNION ALL

SELECT 'Row Seven'

UNION ALL

SELECT 'Row Eight'

SELECT * FROM #DawnTest

DBCC CHECKIDENT('#DAWNTEST', RESEED, 500000)

INSERT #DawnTest (myData) SELECT myData FROM #DawnTest

SELECT * FROM #DawnTest

DELETE #DawnTest WHERE id < 500000

SELECT * FROM #DawnTest

DROP TABLE #DawnTest

-----------------------------------------------------------------

If you have a lot of columns in the table, you can run this code to create a column list that you can cut and paste into the above query:

DECLARE @columnList varchar(8000)

, @tableName varchar(50)

SET @columnList = ''

SET @tableName = 'DawnTest'

SELECT @columnList = @columnList

+ CASE WHEN @columnList = '' THEN '' ELSE ', ' END

+ c.name

FROM sysobjects t

JOIN syscolumns c

ON t.id = c.id

WHERE t.name = @tableName

ORDER BY c.colid

PRINT @columnList