Copy tables to another SQL Server keeping the identity property

Question

Objective

I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing.

Problem

The copied tables all have the identity property on their ID column set to 'no'. As far as I understand, that setting allows the column to auto-increment (I get an error when I try to insert values without giving a value for ID).

The tables on the server all have this setting set to 'yes' and now when I try to set it to 'yes' on the local copies, my software (SQL Server Management Studio) asks me to drop and re-create the whole table and therefore losing all the data.

What I've tried

The following solutions all copy the tables with their data, but don't copy the identity property:

• Copy the tables each with a query (using linked servers): SELECT * INTO NEW_DB.dbo.tblCopy FROM OPENQUERY([server], 'select * from OLD_DB.dbo.tblData')
• Import the data with Task -> Import Data... (Using this tutorial)
  • I tried this both with "Enable identity insert" checked and unchecked
• Create the table on the destination database and simply copy/paste the data into it (this did not work at all - no data was inserted)

Notes

• I would rather not rely on third-party tools. I am using SQL Server Management Studio 2018.
• I am aware of this question on Stack Overflow which talks about the exact same problem. But as already stated above, enabling "enable identity insert" did not copy the identity property.
• I am also aware of this other question asked on Server Fault, both of its answers are about a third-party tool though, which I do not want to use.
• I only have read permission on the source server/database.
• The tables contain up to 6,000 rows.
• The source database contains several more tables, I only want to copy 10 specific tables.

Answer 1

It might be easiest for a one-off exercise to use the Generate Scripts SSMS feature to script out the tables. Right-click the database, choose Tasks, then Generate Scripts.

Be sure to check the advanced options so you script exactly what you need. That will create the tables (and, optionally, things like indexes) including the IDENTITY property.

You can then load the tables using any convenient method, including the Import and Export Data Wizard you already have experience with. You will need to select the "enable identity insert" option because the destination tables will have that property now.

Yes, it would be nice if the import and export wizard handled identity, but it is a general tool designed for multiple types of data source, and identity is proprietary.

Answer 2

Sounds like a good use case for doing a BACPAC export / import which this article walks you through on how to do for just a subset of tables.

---

Alternatively you should be able to create new empty tables from generating the CREATE scripts of your existing tables on your local instance, add the identity specification to the scripts and run them, SET IDENTITY_INSERT NewTable ON, insert into the new tables from the original local tables, SET IDENTITY_INSERT NewTable OFF, drop the original local tables, and rename the newly populated tables (e.g. with sp_rename).

It's a lot simpler than it sounds, your process in example T-SQL would look like this:

-- All of this happens on your local SQL instance, since you already have the data there from your prior attempt
-- Create your new tables with generated scripts from the old local ones, and add in the identity specification
CREATE TABLE NewTable1
(
    IdentityIdColumn INT IDENTITY (1,1),
    SomeOtherColumn VARCHAR(100)
    ...
)

-- Set identity insert on
SET IDENTITY_INSERT NewTable1 ON

-- Insert your data from your old local tables
INSERT INTO NewTable1 (IdentityIdColumn, SomeOtherColumn, ...)
SELECT IdentityIdColumn, SomeOtherColumn, ...
FROM Table1

-- Set identity insert off
SET IDENTITY_INSERT NewTable1 OFF

-- Drop old local tables
DROP TABLE Table1

-- Rename new tables (e.g. NewTable1 => Table1)
EXEC sp_rename 'NewTable1', 'Table1';

Note you can only have one table with IDENTITY_INSERT on at a time. So while you can batch all your tables for the other steps, you'll need a specific step to turn IDENTITY_INSERT on, do the insert from old table to new table, and turn it off for each table you need to process.

Answer 3

Where I work we use SymmetricDS.

It has a solid user interface.

Users select source database tables and fields - and destination tables and fields.

The field names can differ over databases.

The SymmetricDS team provides really solid support.

And their software is downloadable and you don't need support from them to set things up on your own.

It creates triggers on the source database. Into a dedicated schema. All copied data makes it's way to the sym_data table - so you can monitor activity.