We help IT Professionals succeed at work.

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

pauldes
pauldes asked
on
Medium Priority
680 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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

Author

Commented:
But how do I do it outside of SQL EM? I have 400+ tables to deal with.
Commented:
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

Author

Commented:
Good point......

Commented:
Thanks  :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.