Don't Understand How To Convert int column to int IDENTITY column......

So, now I want to take an int column that already has sequential integer data in it and convert it to int Identity, seed = 1, increment = 1, No_Nulls

I've been researching and the easiest thing for me to come close to understanding was the following BUT I still don't quite understand how it works. It seems kind of out of order to me and that it would somehow delete the table with the data I wanted in it.

Could someone give me a clue? Let's say I wanted to do this to a table called CARS. How would I edit this to run the script on the CARS table?

CREATE TABLE dbo.TestTable (Test int NOT NULL)  
GO
INSERT INTO dbo.TestTable (Test) VALUES (5)
GO
CREATE TABLE dbo.TestTable_New (Test int IDENTITY(1, 1))  
GO
SET IDENTITY_INSERT dbo.TestTable_New ON
GO
INSERT INTO dbo.TestTable_New (Test)
SELECT Test FROM dbo.TestTable
GO
SET IDENTITY_INSERT dbo.TestTable_New OFF
GO
select Test from dbo.TestTable
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.TestTable_New',N'TestTable','OBJECT'
GO
select Test from dbo.TestTable
GO

Thanks
pauldesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adwisemanCommented:
In enterprise manager, go to the design view of the table.  Select the field you want to turn into an identity field, and change it's identity property to YES.  It's defaults will seed1, inc 1.  That's it.
gad_flyCommented:
Hi
as adwiseman say true , you must check your table ( if the column have data on it) to be not null and all the value is unique .

then you can make this change in enterprise manager and your data will be safe.

Mohammad Pourebtehaj
pauldesAuthor Commented:
But how do I do it outside of SQL EM? I have 400+ tables to deal with.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

gad_flyCommented:
hi !!!

400+ table !! all of them is the  same ?
if not why you are asking this Q !!!

400 or 1 table when they are not like each other you must do it one by one ! ok ?

and then it's better to check all the tables with my last suggestion ... i think The Important Object in the tables is the Data :)) and perhaps you dont wanna lose them right?

Mohammad Pourebtehaj

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pauldesAuthor Commented:
Good point......
gad_flyCommented:
Thanks  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.