In SQL Server, what's the best way to merge tables from multiple databases?

Question

hi guys,

I'm sorry that I can't find a better title of my question. Not lemme describe it in detail.

I have 4 database which are a, b, c and d. Database a have all table's that appear in b, c and d, and they have the same structure with the same constraints(pk, fk, default, check). b, c,d just have some tables that appear in a. Now there already some data in a, b, c and d. In b, c,d there are more data than the counterparts in a. And probably a have duplicated data with b, c,d.

Now what I want to do is export all data in b, c,d and import them to a. I already have a solution but I want to know what is the best method to do such a complicated task.

Thanks.

Answer 1

You can perform a Insert Into statement with the use of a unions that obtains the results from other databases

Insert Into dboTableA(ID, Value)
Select ID, Value From dbo.DatabaseB.TableA
UNION AlL
Select ID, Value From dob.DatabaseC.TableA
UNION ALL
Select ID, Value From dbo.DatabaseD.TableA

Answer 2

The UNIONs (no ALL) in the subquery will remove duplicates. Then the IS NULL in the Where will only insert new rows into Table1.

Insert Into DatabaseA.dbo.Table1(ID, Value)
Select ID, Value
FROM (
    Select ID, Value From DatabaseB.dbo.Table1
    UNION
    Select ID, Value From DatabaseC.dbo.Table1
    UNION
    Select ID, Value From DatabaseD.dbo.Table1
    ) T
LEFT JOIN DatabaseA.dbo.Table1 S ON T.ID = S.ID 
WHERE S.ID IS NULL