Copy Table From One Database to Another Database in SQL Server

Users of SQL Server Database work with various database components including Table, Views, Stored Procedures, etc. In many cases, user may need to copy the SQL Server table schema and data from one database to another database in the same instance or different SQL instance. In the article, we will learn about different ways through which user can copy table from one database to another SQL Server.

Note: For this article, we will use Database1 as source and Database2 as destination database. The table that will be copied from Source to Destination is Department table under Computer Schema.

Let us learn about the different methods that will help user in copying the table from one database to another:-

Using SELECT INTO Query

The first method includes usage of SELECT INTO command for copying table from one database to another. The following syntax will be used:

SELECT * INTO DestinationDB.dbo.tablename from SourceDB.dbo.SourceTable

The statement will create the tables in the destination database first and then copy the data to these tables. However, if we want to copy objects, indexes, triggers, etc. we need to use the third method Generate Scripts.

Example:

“SELECT * INTO Database2. Computer.Department from Database1. Computer.Department”

The columns in destination table are created in the order defined in the select statement. All the columns in the table have the exact name and data type as in the source database. If we want to copy only the ID and name in table Department from Database1 to Database2, we will use:

“SELECT ID, Name into Database2.Computer.Department from Database1.Computer.Department”

Using Export/Import Wizard

Second method involves using SQL Server Export and Import Wizard, which is present in SQL Server Management Studio. The user can either export from the Source database or import from Destination database in order to copy the data.

Steps that need to be followed are:

• Launch SQL Server Management Studio.
• Select and right-click on the Source Database, go to Tasks > Export Data.
• Import/Export Wizard will be opened and click on Next to proceed.
• Enter the data source, server name and select the authentication method and the source database. Click on Next.
• Now, enter the destination, server name, authentication method and destination database then click on Next.
• Select ‘Copy data from one or more tables or views’ option in the next window and click on Next
• In the next screen, user needs to select the tables that need to be copied from chosen source database to destination database. Click on Next.
• Click on Next to move further and select Finish to close the wizard.
• After completion, a status report will be generated and message for successful execution will be displayed.

This method provides an easy way to copy the tables from source database to destination database; however, it cannot be used to transfer the SQL Server Table’s indexes and keys.

Using Generate Scripts

The last method is done by generating scripts and it will help user in copying not only the table schema or data, but also allows user to copy views, functions, constraints, triggers, etc.

Steps to generate the scripts are as follows:

• Launch SQL Server Management Studio.
• Select the source database and right-click on it, go to Tasks > Generate Scripts.
• Generate and Publish Scripts wizard will be opened. In Choose objects page, select the specific database objects to choose the tables and click on Next.
• Next is Set Scripting Options page, define the path to save the generated script file and click on Advanced option.
• On Advanced Scripting Options window, select Schema and data in the ‘types of data to script’ and click on OK.
• User can review the summary window after selecting all the options then, click on Next.
• Progress report can be checked and in the end, click on Finish to close the wizard.

The method of generating scripts is useful in generating one single script for the table’s schema, data along with indexes and keys. This method cannot create the script in correct order in case there are relations between the tables.

Conclusion

The article has includes various methods to copy table from one database to another SQL Server Database. Each method has been explained in depth with steps to guide users in copying the table’s data easily. It further includes limitations that are associated with every copying method. However, if you do not want to go with these lengthy procedures then you can also try a handy third party SQL MDF repair tool to export table data from one database to another database in SQL Server.