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?
 
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
0
 
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.
0
 
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
pauldesAuthor Commented:
But how do I do it outside of SQL EM? I have 400+ tables to deal with.
0
 
pauldesAuthor Commented:
Good point......
0
 
gad_flyCommented:
Thanks  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.