Copy Table From One Database to Another Database with Best 4 Ways

In today’s digital era, SQL (Structured Query Language) Server is the most prominent relational database management system (RDBMS). However, there are instances when users want to copy table from one database to another in SQL Server.

If you are searching for solutions to copy data from one table to another table in different databases, this blog provides you with the best methods for data transfer.

SQL Server Copy Table to Another Database – All 4 Methods

There are multiple solutions (total 4 best methods to be precise) available to transfer data from one database to another. We will mention all of these here:

Manual Solution (Error-Prone Solution)
– Using SELECT INTO Query
– Import/Export Wizard
– Generate Script Method
Automated Tool (Tried and Tested Solution)

Workaround 1: Copy Table From One Database to Another Manually

As we said earlier, there are three options for copy data from one table to another table in different database in SQL manually. Before starting the procedure, make sure that you have in-depth technical knowledge and sufficient time to run the query. Any mishap could lead to a huge amount of data loss.

Solution 1: Copy Table From Database to Another SQL Server Using SELECT INTO Query

In this section, we discuss the “SELECT INTO Query” method to perform the data copy task. With this, you can create a new table in the destination database.

Before starting the “SQL Server copy table to another database” procedure, let’s assume Database 1 is a source and Database 2 is a destination database.

Let’s delve into the step-by-step procedure to “SQL copy table to another database”:

Now, the basic command simply looks like this :

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

Step 1: First, run the following command

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

Step 2: Next, click on F5 to execute the above command.
Step 3: Then, click the f8 to open “Object Explorer”.

Solution 2: Copy Data From One Database to Another Via Export/Import Wizard

Another solution to copy table from one database to another is using the Export/Import wizard in SQL Server Management Studio (SSMS). It is a simple yet effective approach to accomplish the task. This technique allows users to select specific tables, map columns, and configuration options for the export/import process.

Step-by-step procedure on “how to move table from one database to another”:

1. Open the SQL Server Management Studio (SSMS) in your desktop and connect to the instance.
2. Thereafter, Go to the “object explorer” section and right-click on the “Source DB”
3. Now, navigate to Task and select the “export data” command.
4. Here, mention the server name, authentication, and source Db. Then, simply Hit “Next” to proceed further.
5. Afterwards, provide the destination database name.
6. A new window will open up, select the first option that says “copy data from one or more tables or views”. Then, click the “next” button.
7. In the prompt window, select the desired table that you want to copy to a different database. And, Hit “Next”.
8. Under the save and run package, click the “next” button.
9. At last, Click the “Finish” button.

Solution 3: SQL Server Copy Table From One Database to Another Different Server via Generate Scripts

Generating a script might be a tricky task but it offers robust features that copy not only the table structure, but also additional elements such as views, functions, triggers, and beyond. Furthermore, it uses the Microsoft SSMS platform to perform the entire task effectively.

Follow the below-mentioned steps :

1. Open the SQL Server Management Studio (SSMS) and simply connect with the instance. on your System.
2. Now, Select the source database and simply right-click on it.
3. Go to the “Tasks” bar and hit “Generate Scripts”.
4. Under the Generate and Publish Scripts wizard, select the “object page”>> choose “tabes” from the database. Then, hit “next”.
5. In the set scripting option, set path and save the generated script file. Then, hit the Advanced option.
6. Under the Advanced Scripting Options window, choose “Schema as well as data” in the ‘types of data to script’ section and hit OK.
7. After selecting the “next” button, review the summary window.
8. Finally, check the progress report and Hit the “Finish” button to close the wizard

Read More: How to Resolve Microsoft SQL Server Error 2

Workaround 2: SQL Server Copy Table to Another Database Automatically

The manual solutions mentioned above require significant brainstorming and they might be complex for non-technical users. If you want an easier and more effective alternative to moving tables from one database to another, then opt for the RecoveryTools SQL Database Migration Tool.

It is an enterprise-grade level software that allows users to migrate different database objects including – Tables, Functions, Views, etc. The utility can repair the corrupted database objects before copying them to the desired destination. Additionally, the software is compatible with MS SQL Server versions – 7, 8, 10, and 11 with all the SQL Server versions including 2022, 2019, 2017, 2016, and the below versions.

Step-by-step procedure to copy table from database to another SQL Server:

Step 1: Download and run the software on your computer system.
Step 2: Thereafter, click the “open” button to Add the SQL file and choose “online” or “offline” mode.
Step 3: Now, hit the “export” option to continue the task.
Step 4: Afterwards, select the “Destination database” and data objects (table).
Step 5: Lastly, click the “export” button.

By following these steps, the user can easily copy data from one table to another table in different database in SQL Server.

Wrapping Up

In this blog, we explained how to copy table from one database to another. We covered two approaches – manual and expert solutions to perform the task. However, manual solutions are not a good choice to do this.

Therefore, we highly suggest you go with the expert solution. It is the best and most reliable solution for transferring data from one database to another SQL Server. Also, the utility provides a free demo version that allows users to try out the software features & functions before purchasing it.