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.