This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large.
You could save out your data, drop and rebuild the table with the auto-increment and primary key, then load the data back in.
I'll walk you through with an example:
Step 1, create table foobar (without primary key or auto-increment):
CREATE TABLE foobar(
id int NOT NULL,
name nchar(100) NOT NULL,
)
Step 2, insert some rows
insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');
Step 3, copy out foobar data into a temp table:
select * into temp_foobar from foobar
Step 4, drop table foobar:
drop table foobar;
Step 5, recreate your table with the primary key and auto-increment properties:
CREATE TABLE foobar(
id int primary key IDENTITY(1, 1) NOT NULL,
name nchar(100) NOT NULL,
)
Step 6, insert your data from temp table back into foobar
SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;
Step 7, drop your temp table, and check to see if it worked:
drop table temp_foobar;
select * from foobar;
You should get this, and when you inspect the foobar table, the id column is auto-increment of 1 and id is a primary key:
1 one
2 two
3 three