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

Introduction

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 that contains a copy of the table with data from the source database.

There are some simple methods to do this that are described here. So let's look at a practical example of how to copy a table with data from one database to another in an SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.

Creating a table in SQL Server

Now we create a table named employee using.

CREATE TABLE [dbo].[Employee]  
(  
    [EmpID] [int] NULL,  
    [EmpName] [varchar](30) NULL,  
    [EmpSalary] [int] NULL  
)

The following is the sample data for the employee table.

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

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:

SELECT * INTO Model.dbo.[Employee] FROM Master.dbo.[Employee]

Now hit F5 to execute it.

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., 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...."