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

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

LVL 11
tobzzzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Lee SavidgeConnect With a Mentor Commented:
Tools -> Options -> Designers -> Table and Database designers -> untick Prevent Saving changes that require table re-creation.
0
 
tobzzzAuthor Commented:
Ah fantastic, thank you so much :-)
0
 
Lee SavidgeCommented:
No probs. Its ticked by default in SQL 2008 and it's caught a lot of people by surprise including me :)

Lee
0
All Courses

From novice to tech pro — start learning today.