SQL Server – Copying Tables to Another Server While Keeping Identity Property

identitysql serversql-server-2008ssms

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.

Best Answer

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.

Related Question