Solved

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

Posted on 2009-05-06
4
785 Views
Last Modified: 2012-05-06
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
Comment
Question by:tobzzz
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 25 total points
Comment Utility
See if you have the options turned off, see the image.  Go to Tools>Options>Designers
SQL2008Options.png
0
 
LVL 8

Expert Comment

by:k_rasuri
Comment Utility
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
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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
 
LVL 11

Author Comment

by:tobzzz
Comment Utility
@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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now