In this article we’re going to show you a quick and easy way to copy a table in SQL Server. The new Copy SQL Server Table feature in SQL Spreads means that you can copy a table from one database to another in just a few clicks from within Excel.
Introduction
Although it’s not something that you’re likely to do every day, knowing how to copy tables and their contents from one database to another one (possibly in a different SQL instance) can come in very handy. A typical use case might be that you want to copy specific tables from a production database to a development one so that you can do some testing.
To copy tables you would typically use the SQL Server Import and Export Wizard or a script in Management Studio (e.g. using the CREATE TABLE statement), but there’s a much easier way – the Copy SQL Server Table feature in SQL Spreads.
SQL Spreads is a lightweight Data Management solution for SQL Server that uses Excel as the front-end for managing the data in SQL Server. You can use it to connect to SQL Server and load tables into Excel to view and edit data before saving the changes back to the database. Other features include look-up columns, data validation, change tracking and pivot tables.
In the first example below we’re going to copy a table from one database to another, and in the second one, we’re going to make some updates to the data in the copied table and save the changes back to SQL Server.
To follow along with the example, make sure you have the following:
- An instance of SQL Server (localhost) hosting the AdventureWorks2019 database
- A target database to copy the table to; eg DEV
- Excel with the SQL Spreads Add-In installed
Copy SQL Server Table in SQL Spreads
In this example we’re going to connect to the AdventureWorks2019 database from within Excel. We’re then going to copy the HumanResources.Employee table to the DEV database.
- Open Excel and click on the SQL Spreads ribbon menu. Click on the SQL Server Tools button and then click on Copy SQL Server table.
- If this is your first time using SQL Spreads, you’ll be prompted to connect to a SQL Server instance. Enter the relevant information in the Connect to Microsoft SQL Server dialog and then click OK.
- Once you have connected to your SQL Server, the Copy SQL Server table dialog is displayed and we can now select the table we want to copy, and the database we want to copy to. We can also specify the name of the copied table and the schema. By default the data will also be copied to the new table, but you can uncheck the Copy Data option if you want to.
- There is one other cool option in the dialog – you can select to create a new database to which the source table will be copied. Click on the Create new database option in the top right to open a dialog where you can enter the name of the new database.
- That’s it! In just a few clicks, we’ve copied a table from one SQL database to another one – all from within our Excel SQL Spreads environment.
Update the data in the copied table
In the example above we used the new Copy SQL Server table function to copy a table from the AdventureWorks2019 database to a DEV database. Now let’s use SQL Spreads to update some of the records in the new table and then save the changes back to SQL Server
- First, click on the Open Designer button in the SQL Spreads ribbon menu. In the SQL Spreads Designer, click on the DEV database to expand the list of tables, and select the dbo.Employee table. Once selected, the table will be loaded into Excel and you can start making changes to the data.
- Once you’ve made a few changes to the data, click on the Save to Database button. The changes will now be saved back to SQL Server.
Summary
When you are working with data in SQL Sever, you’ll sometimes come across the need to copy a table in SQL Server. There are several ways to achieve this using scripts in SQL Server Management Studio or the SQL Server Import and Export Wizard. However, if you want a really quick and easy solution – the Copy SQL Server Table feature is the one to use.
The Copy SQL Server Table feature is part of the new SQL Server Tools feature in SQL Spreads. With SQL Server Tools you can perform several SQL Server admin tasks from within the familiar environment of Excel:
- Create tables in SQL Server from data in Excel
- Modify columns in SQL tables (column name, data type, default value, primary key, allow null)
- Copy tables in SQL Server
Being able to perform these tasks using SQL Spreads is quicker and easier than using SQL Server Management Studio. Download a trial version of SQL Spreads to test out these great new features.
