MSSQL Import/Export/Copy IDENTITY_INSERT problems

Question

Using MS SQL Server Management Studio 2008.
I have a db (say ip 10.16.17.10 and called db1) and a second one (say ip 10.16.17.25 called db2).

I am trying to copy one table (and its contents) from db1 into db2.
I have the database on both (but empty in db2).

The problem is no matter how I copy/export/import, no matter what options I set in MS SQL Server Management Studio 2008 when I click 'table'->'Design' (on db2) it ALWAYS says 'Identity Spefication: NO' even tho the db1 table has it on.

From db1 I go to 'Tasks'->'export'->'source/db' and 'destination/db'->'Edit Mapping'->'Enable identity Insert' and click it on.

But no joy. ALWAYS exports without it.
I try similar thing from IMPORT on db2. Similar thing if I use COPY.

I have read MANY of the STACKOVERFLOW articles on this, they all suggest setting IDENTITY_INSERT setting to ON but when I do run below:

SET IDENTITY_INSERT [dbo].[mytable] ON

The table either doesn't exist yet or has already copied WITHOUT the identity setting on so see the error:

does not have the identity property. Cannot perform SET operation.

I have tried setting it as a property (under database properties) for db2 but when I copy/import/export never works.

Would appreciate any help here as lots of StackOverflow articles so far all seem to be having an easier time than me.

I am planning on doing this for another 50 or so tables in this database so am hoping to find a way which doesnt involve running scripts for each table.

thanks

Answer 1

The process of using the Export Data Wizard to copy the data from one table to another will NOT replicate all aspects of the schema (like identity and auto-increment). If you want to replicate the schema, script out your table into a create statement, change the name to db2, and create it. Then you should be able to run the export/import wizard with the identity insert option on and insert into your new table that replicates the schema of your old table.