Problem
There are times when it is necessary to copy table data using SQL Server native tooling. Does SQL Server have the capability to do so natively? Teams might request that a specific table be copied from a Production environment to another environment. This scenario can be when the techniques listed below will come in handy.
Solution
A couple of methods for copying table data from one database on one server to another database on a different server are listed below. These methods all take advantage of using tools natively found in SQL Server.
Method one will demonstrate using the Import/Export Wizard, a GUI used to select the Source and Destination Information. This point and click tool makes it easy to accomplish the task at hand.
The next method will show how to use T-SQL along with Linked Servers to copy the same data across to a different server.
Setup Environment
In our example, we will be using the StackOverflow database that is available free at this link. This was downloaded and restored with database name StackOverflow to our Production instance (sql2017). On our Development instance (dev2017) I created an empty StackOverflow database where the data will be copied to. For this testing, both instances are on the same machine, but this will work for instances on different servers.
Using the SSMS Import and Export Wizard
The Import/Export Wizard is a solution that is part of the SQL Server Management Studio application. This application's primary purpose is to copy data from a Source system to a Destination system with ease.
To access the Import/Export Wizard, open SQL Server Management Studio.
Connect to the Production instance where you stored the source data.
From the database list, locate the StackOverflow database and right click and choose Tasks > Import Data and the below screen will open.
The first section to be configured is the Source Data, which is our Production instance.
Settings to Configure for Source:
- Data Source = SQL Server Native Client
- Server name = Production Instance Name
- Database = StackOverflow
Click Next to continue.
Settings to Configure for Destination:
- Data Source = SQL Server Native Client
- Server name = Development Instance Name
- Database = StackOverflow
Click Next to continue.
On the Specify Table Copy or Query screen, leave the default "Copy data from one or more tables or views" selected.
Click Next to continue.
Check the box next to the dbo.Users table on the left side (Source) and this will then populate the right side (Destination) table as dbo.Users which can be changed if you want.
Click Edit Mappings to open the window below that allows you to configure the table copy settings.
Settings to Configure Column Mappings:
- Delete rows in destination table = Enabled
- Enable identity insert = Enabled (This setting maintains the Id column value on the data copy)
Verify that all the columns in the Mappings section for Source/Destination match because the table schema is the same between Development and Production.
Click Ok to save the changes.
Click Next twice to continue.
The last step to begin the copy process is to click Finish.
The above image shows 11 million records from the dbo.Users Production database has been transferred to the Development dbo.Users table and is ready for developers.
See the comparison record count showing Production and Development match.
Using a SQL Server Linked Server to Copy a Table
Using a Linked Server gives the user multiple options for copying data from one server to another. Three T-SQL techniques will be reviewed below.
The first thing we will do is create a Linked Server which can be used by all three methods. The Linked Server is created on the Development instance and points to the Production instance.
Below is simplified syntax, but refer to this article for more linked server details, if needed.
-- Create Linked Server from Dev to ProdUSE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'localhost\sql2017', @srvproduct=N'SQL Server' GGO -- Validate Linked Server is working SELECT name FROM [localhost\sql2017].master.sys.databases GO
Example: SELECT INTO
The first method will use the SELECT INTO syntax to copy the dbo.Users table from the Production instance to the Development instance. This will create a new table called dbo.Users_SELECT-INTO on the Development instance.
Connect to the Development instance where you plan to store the source data.
Open a New Query Window and paste in the code.
-- Login to Dev Server and execute query SELECT * INTO [StackOverflow].[dbo].[Users_SELECT-INTO] FROM [localhost\sql2017].[StackOverflow].[dbo].[Users]
The first query will create a Linked Server on your Development instance that points back to your Production instance.
For the next query, we will confirm that we can read the sys.databases table from the Production instance.
Lastly, the query will do a copy of the dbo.Users table from Production to Development.
The copy of 11 million records took five minutes to complete.
Example: SELECT INTO using OPENQUERY
The second method will use the SELECT INTO syntax with the OPENQUERY option to copy the dbo.Users table from the Production instance to the Development instance. This will create a new table called dbo.Users_SELECT-INTO-OPENQUERY on the Development instance.
Connect to the Development instance where you plan to store the source data.
Open a New Query Window and paste in the code.
SELECT * INTO [StackOverflow].[dbo].[Users_SELECT-INTO-OPENQUERY] FROM OPENQUERY([localhost\sql2017],'SELECT * FROM StackOverflow.dbo.Users'
We accomplished the same 11 million record copy with this method, but it took only three minutes to complete.
The OPENQUERY syntax allows you to pass a query through the linked server. More information can be found in the MS Docs link.
Example: INSERT INTO
For the final example, INSERT INTO syntax will be demonstrated to copy the dbo.Users table from the Production instance to the Development instance. This will result in a new table called dbo.Users_INSERT-INTO on the Development instance.
Connect to the Development instance where you plan to store the source data.
Open a New Query Window and paste in the code.
-- Login to Dev Server and execute query SET IDENTITY_INSERT [Users_INSERT-INTO] ON INSERT INTO [StackOverflow].[dbo].[Users_INSERT-INTO] ([Id], [AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId]) SELECT * FROM [localhost\sql2017].[StackOverflow].[dbo].[Users] SET IDENTITY_INSERT [Users_INSERT-INTO] OFF
With the INSERT INTO syntax there are a couple extra steps involved. You must use the IDENTITY_INSERT co command to allow the Id column to properly copy the value from the Production Server to the Development Server table (this is only needed if the table has an identity column). The column list must also be specified in the INSERT statement if the table has an identity column and you use SET IDENTITY_INSERT.
You can see that we have copied the 11 million records from Production to Development in just over seven minutes.
Next Steps
- The best path forward would be for you to test and choose the best plan that works for your environment.
