Defining an int identity field
Wow it seems like I am the main question contributor to this forum! Heh, oh well.
My problem today is this. I am using SQL Server 6.5 and have a table that I need to add a column to. Unfortunately my client didn't plan out the project as well as he should have, and he has already started using the db (has about 5000 records in it). Since SQL Server doesn't have any ALTER TABLE type functions, I need to create a new table and import all the information over.
But when I did a Insert INTO Table2 Select * FROM Table1, it threw me an error because I have a unique identity field that it wouldn't allow me to set manually. Some of the records in the database have been deleted, so the ID column doesn't have all the values between 1 and 5000. Can I force SQL Server to let me define my int identity field?
Gil Hildebrand, Jr.
Internet Consultant
New Orleans, LA
Peter J. Boettcher posted this at 14:20 — 22nd February 2001.
They have: 812 posts
Joined: Feb 2000
Gil,
SQL 6.5 should support the ALTER TABLE functions. Try this:
Use Database_Name
ALTER TABLE Table_Name
ADD Identity_Column_Name integer
identity(1,1)
In the identity part the numbers in brackets represent what number to start with, and the increment. So if you wanted to start at 100 and go up by 10 every new record you would have identity(100,10)
As for putting you own values into an identity field, this is not going to happen. The only thing I can think of is to set the column up as a regular column first, add you numbers to it, then set it up as an identity field.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.