Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • 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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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