Merge Two Databases Sql Server 2008

So, you’ve got two SQL Server 2008 databases, and you need them to become one. Congratulations, you’ve just volunteered to be the database marriage counselor—except no one gets cake, and the divorce rate is 100% if you mess up.

The "Just Copy-Paste" Illusion

Your first instinct might be to open both databases, hit Ctrl+A, Ctrl+C, then Ctrl+V. That’s adorable. That’s like trying to merge two ice cream flavors by throwing the entire cartons at each other—you end up with a sticky mess, a broken floor, and your boss asking why the sales data now tastes like refunds.

SQL Server 2008 is old, grumpy, and has zero built-in “merge these two dbs” magic button. But don’t panic. We’re going to do this with the finesse of a circus juggler who also knows T-SQL.

Step One: Stop, Drop, and Backup

Before you touch anything, take a full backup of both databases. Not a “quick copy” of the .mdf file while the server is live—that’s like yanking a tablecloth from under a wedding cake and hoping the plates stay put. They won’t.

Use BACKUP DATABASE to create a proper .bak file. If you skip this, the next few hours will be you explaining to your manager that “the data is gone, but I learned a lot about myself.”

The Great Schema Showdown

Now, you need to compare tables, columns, and keys. Think of this as a dating app for data structures—you’re looking for matches, near-matches, and deal-breakers. In one database, “CustomerID” might be an int. In the other, it’s a varchar(50) that contains things like “CUST-001” and, I swear, one entry that says “Bob’s Mom.”

Write a script to list all tables from both. Use sys.tables and sys.columns—and brace yourself. You’ll find that one table named “Temp” in DB1 has 12 columns, while the same table in DB2 has 7, plus a column called “MysteryFlag” with no comments.

Merging Data Like a Boss (or a Gambler)

For the actual insert, you have a few options. The simplest is to use SELECT INTO or INSERT INTO ... SELECT from the source to the target database, but only after you’ve mapped columns manually. Surprise fact: SQL Server 2008 doesn’t have a native MERGE across databases—you’d think it would, but that’s like expecting a flip phone to run TikTok.

If you have overlapping primary keys, you’ll need to decide who wins. My advice? Use NOT EXISTS to skip duplicates, unless you enjoy firing employees. For example:

INSERT INTO TargetDB.dbo.Customers (ID, Name)
SELECT ID, Name FROM SourceDB.dbo.Customers s
WHERE NOT EXISTS (SELECT 1 FROM TargetDB.dbo.Customers t WHERE t.ID = s.ID);

That little line is your best friend. It’s like telling two guests at a wedding, “Only one of you can sit here,” and then letting the second one stand—politely, with a data-type conversion error.

Identity Crisis (Literally)

If your tables use IDENTITY fields, merging can cause duplicate values. Turn on IDENTITY_INSERT for the target table, but only if you’re ready to babysit it like a toddler with scissors. And don’t forget to reseed after the merge—otherwise, your next insert will start at 1 and throw a party on top of existing rows.

The Final Act: Change Your Trousers

After the merge, run DBCC CHECKDB on the target database. This is your sanity check. If it returns errors, you didn’t just fail—you failed loudly, which is somehow worse. Also, verify row counts with COUNT(*) on a few key tables. If the numbers don’t match, don’t panic. Just re-read this article, but slower, and with a glass of something strong.

And here’s the kicker: SQL Server 2008 is end-of-life—no more support patches. So after you merge, your reward is a database that’s even more fragile than the original two. But hey, you did it. You’re the hero who glued two boats together with duct tape and hope. Now go update your résumé, because future you will need it.