• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

set id column to auto increment on existing table with several hundred rows

I'm using MS SQL Server Management Studio Express 2008.
I've imported tables into a database, the id columns in the tables seem to have lost the "Identity Specification" set as Yes (Is Identity=yes and identity increment=yes also lost under that). When I set it as yes and try to save I see the error msg:
"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created."

Do you know how I can do this, by GUI or QUERY? Is it perhaps permissions on the database that are messed up or is it normal that I cannot alter the table design witrh the above error? The database was designed originally in the 2005 Express version, now running in 2008 Express.

Thanks...
0
tobzzz
Asked:
tobzzz
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
See if you have the options turned off, see the image.  Go to Tools>Options>Designers
SQL2008Options.png
0
 
k_rasuriCommented:
I dont know what mentioned by CGLuttrell would work...first you create the table with IDENTITY column.  Then load the data using SET IDENTITY_INSERT <Yourtable> ON. after loading set the IDENTITY_INSERT to OFF
0
 
Chris LuttrellSenior Database ArchitectCommented:
His table already exists with existing data.  2008 by default has it set to not allow changes that require table re-creation.  If you turn this off, you can use the GUI to add the identity property and it will rebuild the table.
0
 
tobzzzAuthor Commented:
@CGLuttrell:
I'm was a little hesitent to do this as I thought unblocking that option would then allow the "drop table" and I didn't want to lose data, but I trusted your Jedi SQL skills and it seems you were correct, I unchecked the box, ran the design amend and it saved without loss of data. Thanks!

@k_rasuri:
I'm not sure what you meant there really but thanks for trying. The above worked just great so I'm sticking with that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now