?
Solved

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

Posted on 2009-05-06
4
Medium Priority
?
807 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 100 total points
ID: 24320443
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
ID: 24320474
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 27

Expert Comment

by:Chris Luttrell
ID: 24320489
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
ID: 24320519
@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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

752 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