Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to set 'is identity' to yes for incrementing id when rows of data already exist

Posted on 2011-09-13
3
Medium Priority
?
258 Views
Last Modified: 2012-05-12
I have a table I imported from Access to SQL Server, it didn't recognise that the column named ID was a primary key column, nor that it was an incrementing identity column.

How do I set 'is identity' to yes for incrementing id when rows of data already exist in the table? When I do this in the tables design using SQL Server Express 2008 r2 it says:
'Saving changes not permitted. The changes you have made require the following tables to be dropped and re-created'!! ...it's a lot of tables and a lot of data, so I need a way of doing this quickly and efficiently.

Please help!

Thanks Experts.

/ Tobzzz

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
3 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 2000 total points
ID: 36528420
Tools -> Options -> Designers -> Table and Database designers -> untick Prevent Saving changes that require table re-creation.
0
 
LVL 11

Author Comment

by:tobzzz
ID: 36528789
Ah fantastic, thank you so much :-)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36528973
No probs. Its ticked by default in SQL 2008 and it's caught a lot of people by surprise including me :)

Lee
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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