I was helping a client change to a new web host. During this process I discovered an Access database was powering part of their web site. This had to go!
I imported the database into SQL Server. Unfortunately, this process removed the identify specifications on the primary key column for one of the tables. This means that the primary key column would not automatically fill in with an automatically incrementing integer. I opened up the table design, and tried to change the field back to an ID. Unfortunately, SQL Server does not allow you to add an identity specification to an existing column on an existing table.
How do I fix that?
What is the Process?
The process was tricky to figure out, but not all that too hard. Basically, do this:
- Create a new table with all the same columns as the old table. Make sure this new table has an identity column.
- Enable Identity Insert on the new table.
- Copy all data from the first table into the new table.
- Disable Identity Insert on the new table.
- Delete the original table.
- Rename the new table to the original table's name.
Then you're done.
What is the Code?
This whole process can be done quickly with a SQL Server script.
First, create the new table:
Then enable the identity insert:
2Go
Next, use this script to insert all the rows from the original table into the new table:
2 FROM dbo.newtable )
3 INSERT INTO dbo. newtable _temp ( Id, textField, bitField /* other fields */)
4 SELECT Id, textField, bitField /* other fields */
5 FROM dbo. newtable TABLOCKX
6go
Then turn off the identity insert:
2Go
Delete the original table:
2Go
Finally rename the old table:
And you should be good to go!
Much thanks to this Stack Overflow question for helping me out.
posted by Jeffry Houser in
Database Design, SQL
| 0 Comments
