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'Tes tTable','O BJECT'
GO
select Test from dbo.TestTable
GO
Thanks
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'Tes
GO
select Test from dbo.TestTable
GO
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
But how do I do it outside of SQL EM? I have 400+ tables to deal with.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good point......
Thanks :)
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