I have a table with 4 million records. I want to change orderid to Identity without losing the data.
Is it possible?
Asked
Active
Viewed 2,281 times
0
Brian Tompsett - 汤莱恩
- 5,438
- 68
- 55
- 126
neoo
- 121
- 1
- 8
-
http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – TheGameiswar Jul 15 '16 at 19:44
-
2See the second answer in the dupe. This is the lowest impact method. – Martin Smith Jul 15 '16 at 19:45
1 Answers
0
Assuming orderid have no duplicates, 1. You can create a new table with orderid as identity column and copy the data. Then drop the existing table 2. Create a new identity column and drop the existing orderid column
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN orderid
sp_rename 'yourTable.NewColumn', 'orderid', 'COLUMN'
Nagaraj Raveendran
- 1,080
- 14
- 23
-
SET IDENTITY_INSERT dbo.[B] ON insert into dbo.[B]( col1, col2, col3, ..., coln ) --List the names of columns select * from dbo.[A] where reqid = 201 SET IDENTITY_INSERT dbo.[B] OFF – neoo Jul 15 '16 at 20:44