0. Do a SELECT MAX() on your current table to see where your numbering
should start.
SELECT MAX(id) FROM table1
1. Create a new table with a temporary name in the new, permanent format.
Be sure you declare the appropriate column as INT IDENTITY.
CREATE TABLE table2 (id INT IDENTITY PRIMARY KEY, foo varchar(20),
bar varchar(40))
2. Copy the existing data into the new table. Be sure you have enabled
IDENTITY INSERT. The existing numbers should not change, despite their
going into an IDENTITY column. Be sure to turn off IDENTITY INSERT when
you're done.
SET IDENTITY_INSERT dbo.table2 ON
INSERT INTO table2 (id, foo, bar) SELECT id, foo, bar FROM table1
SET IDENTITY_INSERT dbo.table2 OFF
3. Run DBCC to reset the identity value on the table. If you don't get a
new identity value similar to what you saw in step 0, you can force it to
a particular value by using the second version below.
DBCC CHECKIDENT (table2, RESEED)
DBCC CHECKIDENT (table2, RESEED, 42)
4. Verify that table2 appears the way you want.
5. DROP TABLE table1
6. Rename table2 to table1
EXEC sp_rename 'table2', 'table1'
_____________________
Eric A. Laney
_____________________
Last night I stayed up late playing poker with Tarot cards. I got a full
house and four people died. - Steven Wright
"Robert Everland III" <[EMAIL PROTECTED]>
04/29/2004 11:38 AM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Alter column to add identity
How do I alter a column that is an int so that it is also an identity? I
can't delete the column as it's an upsized access database so I need to
keep the numbers that are there.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
