SQLTeam.com | Weblogs | Forums

Import table from remote server to local server retaining (Is Identity) on ID column


#1

Imported table has (Is Identity) set to No .I wish it to be Yes but when i try to save it will not allow me.
And will error out on inserts into table
I am using SQL2008 on remote server and SQL2014 on local server.
Any help most appreciated


#2

You may not able to alter the existing columns for identity.

2 options for your requirement,

Create a new table with identity & drop the existing table

Create a new column with identity & drop the existing column


#3

You can do this as follows. However, read the important warning below before you do it. Go to Tools -> Options -> Designers -> Table and Database Designers and uncheck the checkbox that reads "Prevent saving changes that require table re-creation". Then try to change the identity property of the table.

The IMPORTANT HOWEVER WARNING: When you change the identity property to false, internally SQL Server drops and creates the table and makes sure that the data is saved and repopulated to avoid data loss. So if your table has a lot of data, this will be very time-consuming and resource intensive. For tables with few hundred records, this should not be an issue. But if you have a large number of records, test it in a dev environment to make sure that you are not going to freeze up your server for a long time.


#4

Thank you
For your reply i will try it.


#5

Thank you
Works a treat.
Best wishes