Copy Table Schema and Data From One Database to Another Database in SQL Server

This article is all about how to copy SQL Server tables and their data from one database to another database.

Database developers and DBA can use a SQL Query or SQL Server Management Studio or scripts to copy a database table schemas and data from one database to another database in SQL Server. In this article, you'll learn how to copy a database tables and their data from one SQL Server database to another SQL Server database using a query as well as graphically in SQL Server.
 

Introduction 

 
I have created two databases named databasefrm and databaseto. In database databasefrm, I have a table named Emp containing data like ID, Name, Address and so on. I want to copy the table Emp to databaseto including its data. We can do this using various methods. You can use one of these methods.
 

Method 1. Using SQL Query

 
The query required here is:
 
Query Syntax
 
Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable
 
Example
  1. select * into databaseto.dbo.emp from databasefrm.dbo.Emp  
This command only copies the table schema and data. If we want to copy objects, indexes, triggers or constraints then we need to generate Scripts (third method) that we will talk about later in this article.
 
If we want to copy a table in the same database then we need to first use our database then execute this query:
  1. select * into newtable from SourceTable  
Example 
  1. select * into emp1 from emp  
We can also select only a few columns into the destination table.
 
Query Syntax
 
select col1, col2 into <destination_table> from <source_table> 
 
Example
  1. select Id,Name into databaseto.dbo.emp1 from databasefrm.dbo.Emp  
Preview after executing this query:
 
image1.jpg 
 
Here we only copy Id and Name in table emp1 from the Emp table in the databasefrm database.
 
If we want to copy only the structure or the schema of the table then we need to use this query:
 
select *into <destination_database.dbo.destination table>
from <source_database.dbo.source table> where 1 = 2
 
Preview after executing this query:
 
image2.jpg 
 

Method 2. Using SQL Server Management Studio 

 
1. Open SQL Server Management Studio
 
2. Right-click on the database name then select "Tasks" > "Export data..." from the object explorer.
 
image1_2.jpg 
 
3. The SQL Server Import/Export wizard opens; click on "Next"
 
image2_2.jpg 
 
4. Provide authentication and select the source from which you want to copy the data; click "Next".
 
image3_2.jpg 
 
5. Specify where to copy the data to; click on "Next".
 
image4_2.jpg 
 
image5_2.jpg 
 
image6_2.jpg 
 
image7_2.jpg 
 

Method 3. By Generating Script 

 
By using the above two methods we are only able to copy the table schema and data but we are not able to copy views, functions, constraints, triggers and so on. We can copy all these things by generating scripts.
 
Let's see how to generate a script:
  1. Right-click on the database name then select "Tasks" then click on "Generate Scripts". After that the Script Wizard opens. Click on "Next".
  2. Select the Database you want to script.
  3. Choose the object types. Click on "Next".
  4. Select the tables.
  5. Select the Output option for the script.
  6. Change/Edit the database name to the name you want to execute this script for.
Let's see how to do it step-by-step:
 
generate script.gif 
 

Summary

 
In this article, we leared three different ways to copy a SQL Server database tables schema and their data.  The first method used a SQL query. The second method used SQL Server Management Studio and the third method used by generating and executing a script.