How to Merge Two SQL Server Databases & Files

Approved By Debasish Pramanik

Published On January 24th, 2024

Reading Time 5 Minutes Reading

Category SQL Server

The general way to merge SQL server databases is by using the native SQL queries. But that consumes a lot of time and effort since you have to create and test a query before applying it and it’s fairly complex.

This article takes you through a unique approach to consolidate multiple databases in SQL server. It works for both scenarios:

Two databases in a single SQL server
Two databases on different SQL server

The good thing is that even if the server resides on different systems, this method will still work as long as they are connected via a network.

We will even include a way to merge database files (MDF) into one SQL server database at the end of the blog.

SO, let us keep reading.

Method to Merge Two Databases in SQL Server (Same Server or Different)

We will be using SQL Server data migration wizard for this task. The main functionality of this tool is to migrate objects from one server to another. And that includes the complete database. You can even choose the same SQL server as source and destination to facilitate database merging on the same server.

The exact instructions to achieve your required merge operation is as follows:

Download, install and start the migrator wizard on your Windows system.

Download Now Purchase Now

Click the Open button.

click open button

Enter the SQL server name and the authentication type.

source SQL server

Select the database you want to merge to another.

source database

Click Ok to move to the preview window.

click ok

Here, view all the objects of the selected SQL Server database, then click Export to proceed.

Select the SQL Server Database and enter the destination SQL server name here.
- To merge SQL database of the same server, enter the same server name as in Step 2.
- To merge SQL databases of different servers, enter the server name of that server (you can connect the machine on a network if the server resides on a different system).

choose destination server

Select Export to Existing Database and choose the second database that you wish to merge with the previously selected first database.

export to existing DB

Choose the objects that you wish to move to the other database and click the Export button.

Wait for the process to complete and click OK on the confirmation window.

confirmation window

Open the destination SQL server to find all the objects of the source database in the destination database.

This concludes the merge operation for two live databases.

The tool does not contain any duplication checker, so you need to apply redundancy check yourself in the merged databases.

Method to Consolidate SQL Server Database Files (MDF)

This method focuses on merging offline SQL Server Database files i.e., MDF files. It will require a SQL server for the execution of this process. Once you have DB files ready on the system running SQL Server, follow the order of steps given below:

Download, install and run the same Migrator wizard and described in the previous section.
Click on the Open

click open button

Now, select the Offline mode and browse the MDF file from the system.

browse database file

Choose the Scan mode and click the OK

choose SQL scan mode

Note: When dealing with database files, the software also offers to recover deleted objects of SQL database.

When the scanning completes, click the Close

The wizard will preview all your database objects, click the Export menu button to proceed.

export menu

Select SQL Server Database and provide the server name for the destination SQL server.

select database server

Choose to Create new database option and provide a name for the database that will contain merged SQL database files.

select SQL database

Finally, click the Export button to start moving the files into the SQL server database.

export to live database

Wait for the process to complete and click the OK button on the confirmation window.

merge completed

With this, we have moved one database file into the server. Now, repeat all the steps to move another database file into the server.

This way we moved both files in a single database, thus effectively merging two SQL server database files together.

The tool does not contain any duplication checker, so you need to apply redundancy check yourself in the merged database files.

Summary

In this article, we went through the steps to merge two databases in SQL server regardless of the source and destination being the same or different. We also looked at the instructions to consolidate the database files into one. This tool also allows you to generate scripts with data in SQL Server. Make sure to download and run the FREE DEMO version of the tool to execute the merge process once. It will help you in deciding whether this wizard is the right choice for you or not.

FAQs

Can you merge two databases?

Yes, we provided the steps for the same.

How do I join tables from two SQL databases?

Use the following template:

SELECT

table_1.*,

table_2.*

FROM [Database_1].[Table_Schema].[Table_Name_1] table_1

JOIN [Database_2].[Table_Schema].[Table_Name_2] table_2 ON table_1.id = table_2.id

Which merge method is used when we try to merge two different tables into one?

You can use MERGE statement to synchronize two tables by specifying the Source and Target table and the Join condition.