Home » Blog » SQL Server » How to Merge Two SQL Server Databases?

How to Merge Two SQL Server Databases?

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

You can merge two SQL Server databases with four methods. INSERT SELECT with three part names copies rows between databases on one server. The MERGE statement syncs a source table with a destination table. The Import and Export Wizard copies tables with a few clicks. Migration software exports objects into an existing database and merges MDF files. Check duplicate keys and identity columns first.

Companies that merge two SQL Server databases combine two applications or two branch offices. SQL Server has no single command that joins two databases. You copy objects and rows from one database into the other.

Most guides show one query and stop. Few explain duplicate keys, identity columns or what to do when only MDF files remain.

This guide explains four methods to combine two SQL Server databases into one, with the checks before the merge and the tasks after it.

Microsoft documents the cross database syntax in its INSERT statement reference.

Why Merge Two SQL Server Databases?

Three business events trigger most merges:

  • Mergers. Two companies combine their applications on one database.
  • Consolidation. Teams retire a small server and reduce licensing cost.
  • Branches. Regional databases move into a central database for reporting.

The SQL Server MERGE statement does not merge databases. Microsoft describes it as a way to insert, update or delete rows in one table based on another table.

Which Checks Come Before You Merge Two Databases?

Seven checks come first:

  1. Create backups of both databases. Scripts for full, differential and log backups sit in Explore Different Types of Backups in SQL Server.
  2. Compare table names. Rename or prefix tables that exist in both databases with different columns.
  3. Find overlapping primary keys and identity ranges.
  4. Compare the collation of both databases.
  5. Note the foreign keys so parent tables receive rows before child tables.
  6. Decide which database becomes the destination.
  7. Test the merge on restored copies first.

4 Ways to Merge Two SQL Server Databases

Three methods use T-SQL and wizards from Microsoft and the fourth adds software for MDF files.

MethodServerMain limit
INSERT SELECTSame server or linked serverNeeds a script for every table
MERGE StatementSame serverMatching rules need testing
Import and Export WizardSame or different serverMoves data and not every constraint
Migration SoftwareSame or different serverTrial record limit

Method #1: Copy Rows With INSERT SELECT (Free, Same Server)

INSERT SELECT copies rows from a table in one database into a table in another with a three part name. Four steps merge the Customers table:

  1. Open a new query window on the server that has both databases.
  2. Copy the rows that the destination does not have:
INSERT INTO ShopDB.dbo.Customers (CustomerName, Email, City)
SELECT s.CustomerName, s.Email, s.City
FROM StoreDB.dbo.Customers AS s
WHERE NOT EXISTS (
    SELECT 1 FROM ShopDB.dbo.Customers AS c
    WHERE c.Email = s.Email
);
  1. Repeat the query for every table and start with parent tables.
  2. Compare the row counts of both sides.

Note: Omit the identity column from the column list so the destination creates new values. Use SET IDENTITY_INSERT with a column list when you must keep the old values. Microsoft states that only one table in a session can have it set to ON.

For a database on another server add a linked server and name the table as server.database.schema.table.

Method #2: Sync Tables With the MERGE Statement (Free, Updates and Inserts)

The MERGE statement compares a source table with a destination table and inserts or updates rows in one pass. Three steps merge Customers and update the city of matching rows:

  1. Open a query window on the destination server.
  2. Execute the statement with a semicolon at the end:
MERGE ShopDB.dbo.Customers AS tgt
USING StoreDB.dbo.Customers AS src
    ON tgt.Email = src.Email
WHEN MATCHED THEN
    UPDATE SET tgt.City = src.City
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerName, Email, City)
    VALUES (src.CustomerName, src.Email, src.City);
  1. Compare the row counts of both sides.

    Microsoft states that MERGE requires a semicolon and cannot use a remote table as the destination. Plan to test every MERGE statement before production because large copies can cause concurrency issues.

Note: Match on key columns only. Microsoft warns that extra filters on destination rows in the ON clause can return wrong results.

Method #3: Copy Tables With the Import and Export Wizard (Free, Little Code)

The wizard copies tables and views from one database into another. Six steps copy the tables:

  1. Right click the source database in Object Explorer and select Tasks and then Export Data.
  2. Select the SQL Server data source and confirm the source database.
  3. Select the destination and choose the existing database.
  4. Select Copy data from one or more tables or views.
  5. Tick the tables to copy and review the Edit Mappings window.
  6. Choose Run immediately and click Finish.

Note: The wizard moves table data. Create keys, indexes and constraints on the destination by hand and confirm the result. Microsoft explains the wizard in its Import and Export Wizard guide.

Method #4: Merge Two Databases Into One With EmailDoctor (Free Preview, Paid Export)

Teams that want one export for tables, views and procedures use EmailDoctor SQL Server Data Migration Tool. The migration software connects to the first database and exports its objects into the second database through the Export to Existing Database option. Offline mode merges MDF files the same way.

The trial previews every object but exports only 25 records per table and 5 other categories. Paid versions remove that limit.

Connect to the First Database

Five steps connect to the first database and list its objects:

  1. Start the software on a Windows PC and select Online mode.
  2. Add the Server Name with Authentication of the first database. Login window for the first server
  3. Select the database from the Drop Down List and click OK. Database list of the first server
  4. Open the tree to review tables, views and procedures. Preview of the first database
  5. Click Export at the top.

Export Into the Second Database

Six steps merge the objects into the second database:

  1. Choose SQL Server Database as the export type. Export type options
  2. Enter the Server Name with Authentication of the destination server. Login window for the destination server
  3. Select Export to Existing Database and choose the second database. Existing database option
  4. Tick the objects that you want to merge. Object selection
  5. Choose With Schema & Data for structure and rows. Schema and data options
  6. Click Save and then OK on the Export Completed message. Export Completed message

Note: No redundancy check exists in the software. Compare duplicate keys and table names before the export. For MDF files select Offline mode and export the first file to a new database. Export the second file into that database with Export to Existing Database.

Which Tasks Follow a Merge?

Four tasks follow every merge:

  1. Compare row counts and key columns on both sides.
  2. Find duplicate rows with a query on the business key:
SELECT Email, COUNT(*) AS copies
FROM ShopDB.dbo.Customers
GROUP BY Email
HAVING COUNT(*) > 1;
  1. Refresh the statistics:
USE ShopDB;
EXEC sp_updatestats;
  1. Create a fresh backup of the merged database.

    Tell the application team which tables received new rows. Rebuild indexes on the largest tables after the merge.

Which Problems Stop a Merge?

Five problems appear most:

ProblemReasonFix
Duplicate key errorBoth databases use the same primary key valuesMove rows without the key or add an offset
Explicit value for identity columnIDENTITY_INSERT is OFF or the column list is missingSet IDENTITY_INSERT ON and add a column list
MERGE returns an errorSeveral source rows match one destination rowRemove duplicates from the source
Foreign key errorChild rows arrive before parent rowsInsert parent tables first
Collation conflictDatabases use different collationsAdd a COLLATE clause or match the collation

Frequently Asked Questions

Can SQL Server Combine Two Databases Into One?

Yes. Copy the objects and rows from the first database into the second. Use INSERT SELECT, the Import and Export Wizard or migration software.

Does the MERGE Statement Merge Two Databases?

No. The MERGE statement synchronizes two tables. Execute one statement for every table that you need to sync.

Can I Merge Databases on Different Servers?

Yes. Use a linked server with four part names or export from one server into the other with the Import and Export Wizard or migration software.

Will Identity Values Conflict During a Merge?

Yes when both tables use the same range. Omit the identity column so the destination creates new values or use SET IDENTITY_INSERT with a column list.

Does a Merge Remove Duplicate Rows?

No. INSERT SELECT adds every row that your query returns. Use NOT EXISTS or a business key to exclude duplicates. The migration software does not perform a redundancy check either.

Can I Merge Two MDF Files?

Yes. Select Offline mode in the migration software, export the first MDF file to a new database and export the second file into it.

Can I Merge a Newer Database Into an Older Server?

No. Older servers cannot attach a database from a newer release. Move both databases to the newer version first.

Is the Trial Limited for Merging?

Yes. Trial mode exports 25 records per table and 5 other categories only. A license removes that limit.

Author’s Verdict

INSERT SELECT gives the most control for one server and a few tables. The MERGE statement suits tables that need updates and inserts together.

The Import and Export Wizard saves time on many tables. Migration software fits full databases and MDF files.

Test on restored copies. Check duplicate keys before the first row moves.

  • Four methods merge two SQL Server databases.
  • INSERT SELECT copies rows with three part names.
  • The MERGE statement syncs tables.
  • The Import and Export Wizard copies tables.
  • Migration software exports into an existing database and merges MDF files.
  • Check duplicate keys and identity columns.
  • Then compare row counts.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.