Methods to copy tables from one Database to another Database in SQL Server

How to copy the tables from one Database to another in SQL server?

In some cases, as a DBA you are requested to copy the schema and contents of the specific tables from one database to another database on the same instance or to a different instance. Then how do you do this. There are three ways to copy the specific tables from one database to another database

Using the Select into query
Using the Export/Import Wizard
Using the Generate Scripts

Using the SELECT INTO query :

select * into Test1.dbo.Results1 from Partition.dbo.Results

In my above query I’m trying to copying the data of the dbo.Results table from the Partition (Source Database) database to a new table called dbo.Results1 in the database Test1 (Destination database).First it will create the table and then it will copy the data from source table to the destination table.

Using the EXPORT/IMPORT Wizard :

Another method of copying the tables from one database to another database is using the Export/Import Wizard in the SQL Server Management Studio. Below is the step by step process to do this:

1. From the SQL Server Management Studio Right click on Source Database(in my case its Partition) and click on Tasks and then click Export Data to Export the data or Import Data to import the data to this Database. Here I am exporting the Data from this database to another Database so selecting Export Data option.

2. When you click the Export Data the SQL Server Import and Export Wizard appears, then click Next.

3. Choose a Data Source of SQL Server Import and Export Wizard appears. Here in this wizard you need to select the Data source and the Server name (instance name) on which the Source Database lies and also select the source database. Once you finish selecting the options related to Source Database, then click Next.

4. Choose a Destination of SQL Server Import and Export Wizard appears. Here in this wizard you need to select the Data source and the Server name (instance name) on which the Destination Database lies and also select the Destination database. Once you finish selecting the options related to Destination Database, then click Next.

5. Then the Specify Table Copy or Query of SQL Server Import and Export Wizard appears. Here you have two options one is copy data from one or more table or views and the second option write a query to specify the data to transfer. From this two options select the option you want and I am selecting the first option as I want to copy all the data from Source DB to Destination DB, then click Next.

6. Select source tables or views of SQL Server Import and Export Wizard appears in this select the tables you want to copy from source(in my case I selected only one table i.e., dbo.Results ) to destination table( you can rename the table name in the destination if you want. I renamed it to dbo.Results2).To make sure that all the tables will be created in the Destination Database then click on Edit Mappings in the Select source tables or views of SQL Server Import and Export Wizard and then tick the Create Destination Table and if any of your tables contain IDENTITY column make sure to tick Enable Identity Insert and then click OK. Before clicking on OK check the mappings and you can ignore the columns you don’t want to copy to the destination. Then click Next in the Select source tables or views.

7. Complete the Wizard appears and double check everything in this wizard and then click finish.

8. Then the execution successful of the SQL Server Import and Export Wizard appears where you can see the step by step process of copying the data.

Using the Generate Scripts :

Another method of copying the table from one Database to another Database is using the Generate Scripts option. Below is the step by step process to do this:

1. From the SQL Server Management Studio Right click on Source Database (in my case it’s Partition) and click on Tasks and then click the Generate Scripts option.

2. Choose Objects of the Generate and Publish Scripts wizard appears. Under the Choose Objects select the option you want. If you want script the entire database and database objects then select the first option and if you want to select specific database objects then select the second option and select the tables you want to script. Then click Next.

3. Set scripting Options of the Generate and Publish Scripts wizard appears and specify the path where you want to save the script file and click Advanced.

4. The Advance scripting options window appears and select Schema and data under the Types of Data to script and there are other options where you can select like the Table/Views Options. Click OK and then click Next.