Copy Table With Data From One Database to Another in SQL Server 2012

This article demonstrates how to copy a table with data from one database to another in a SQL Server database.

This article demonstrates how to copy a table with data from one database to another in a SQL Server database. In this article, we use a source database containing a table with data and create another database which contains the copy of the table with data from the source database. So let's take a look at a practical example of how to copy a table with data from one database to another in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. There are some simple methods to do that are described here.
 

Creating a table in SQL Server

 
Now we create a table named employee using:
  1. CREATE TABLE [dbo].[Employee]  
  2. (  
  3.     [EmpID] [intNULL,  
  4.     [EmpName] [varchar](30) NULL,  
  5.     [EmpSalary] [intNULL  
  6. )  
  7.   
  8.    
The following is the sample data for the employee table:
 
Employee-table-in-Sql-Server.jpg 
 

Method 1: Copy Table using SELECT INTO

 
This command only copies a table's schema and its data. The Select into is used to copy a table with data from one database to another database's table. The Select into is also used to create a new table in another database. The general syntax to do that is:
 
Syntax
  1. SELECT * INTO DestinationDB.dbo.tableName FROM SourceDB.dbo.SourceTable  
Example
 
The employee table is defined in the master database. It is also called the source database. Now you want to copy the table with data from the master database to the model database. The following query defines it:
  1. SELECT * INTO Model.dbo.[Employee] FROM Master.dbo.[Employee]  
Now hit F5 to execute it. 
 
Select into in SQL Server
 
Now press F8 to open the Object Explorer and select the model database to see the employee table.
 
 

Method 2: Generating Script in SQL Server

 
If you want to copy all objects, indexes, triggers, constraints etc then do it using "Generate Scripts...". Suppose we have a database named Test. Now right-click on the Test database and select the "Generate Scripts..." option.
 
database Name -> "Tasks" -> "Generate Scripts...."
 
Generating Script in SQL Server
 
Now click on "Generate Scripts...". The Generate Scripts wizard will be opened.
 
Generating Script in SQL Server
 
Now click on the "Next" Button and select tables and Stored Procedures.
 
Generating Script in SQL Server
 
Now click on the "Next" Button and provide the proper name with path of the file.
 
Generating Script in SQL Server
 
Now click on the "Next" Button and review your source and target location.
 
Generating Script in SQL Server
 
Now click on the "Next" Button.
 
Generating Script in SQL Server
 
Now finally click on the "Finish" button.
 
The script file has been generated for the Test database. To see the generated script file, select the location of the file in your computer.
 
generated-script-file-in-Sql-Server.jpg
 

Creating a Database in SQL Server

 
These are the following steps to create a new database:
  1. Press F8 to open the Object Browser in SQL Server Management Studio and expend it
  2. Database -> right-click-> select New database
  3. This would open the "New database" window
  4. Now enter a database name to create a database
  5. Now click on the OK button to create the database. The new database will be shown in the Object Explorer
Now the database, named DatabaseTest, has been created.
 
New-Database-in-Sql-Server.jpg
 
 

Copy Database Schema and Data to Other Database

 
Now right-click on the script file and open it in Notepad and copy all the data and paste it in the query window in SQL Server. It will look as in the following:
 
Copy Database Schema
 
Now only change the database name test to DatabaseTest.
 
Query-window-with-change-database-name-in-Sql-Server.jpg
 
Now press F5 to execute the script and expand the databaseTest to see the schema and data.
 
Object-Explorer1-in-Sql-Server.jpg