How can I copy data from one table to another table 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.

Download SQL Server Database Migration Tool For Free

Download Now Purchase Now

Remember: Using this tool you have the option to export either Schema as well as records or export schema only. You can also create a new database for the exported data or export SQL table to the existing database.

Basic Steps to Copy Table From One Database to Another Database in SQL Server Using The Software (Quite Easy to Run):

Step-1: Click on ‘Open’ to load SQL file containing the desired table. Select Online Mode after downloading the software. Provide all the necessary authentication details.

Step-2: In the next window, you can view all the SQL objects and records conatining in that file, Click ‘Export’.

Step-3: Select ‘SQL Server Database’ option and provide the authentication details (as it is for destination server). You can create a new database. You also have the option to export data with only schema or schema and records as well. Finally, click on Export.

Using SELECT INTO Query

Note: To copy table from one database to another 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.

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. This way users can copy data from one table to another SQL server with ease.

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”

Also Read: Users can alsoUpgrade SQL Server 2016 to 2019without any hassles once they complete copy data from one database to another one.

Using Export/Import Wizard

The second method to copy table data from one database to another involves 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.

Also Read: Migrate SQL Server Database to Lower Version of SQL Server

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.

Also Read: Export SQL Server Database to SQL File Format

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 tools to export table data from one database to another database in SQL Server.

User Reviews

Now, let’s quickly have a look at the user reviews that already used this automated solution to get rid of their issue for the same.

How to copy all data from one table to another table in SQL Server?

How do I copy data from one table to another server?

What is fastest way to copy a data from 1 table to another table?