?
Solved

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

Posted on 2006-03-28
6
Medium Priority
?
656 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
0
Comment
Question by:pauldes
  • 3
  • 2
6 Comments
 
LVL 14

Assisted Solution

by:adwiseman
adwiseman earned 1000 total points
ID: 16315235
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16315960
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
 

Author Comment

by:pauldes
ID: 16316333
But how do I do it outside of SQL EM? I have 400+ tables to deal with.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
gad_fly earned 1000 total points
ID: 16316622
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
 

Author Comment

by:pauldes
ID: 16316849
Good point......
0
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316873
Thanks  :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question