Link to home
Start Free TrialLog in
Avatar of pauldes
pauldes

asked on

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
SOLUTION
Avatar of adwiseman
adwiseman

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gad_fly
gad_fly

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
Avatar of pauldes

ASKER

But how do I do it outside of SQL EM? I have 400+ tables to deal with.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pauldes

ASKER

Good point......
Thanks  :)