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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
ASKER
@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.
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.