Solved

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

Posted on 2009-05-06
4
797 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 26

Accepted Solution

by:
Chris Luttrell earned 25 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 26

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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