[SQL] Merging 2 tables from different databases using MERGE INTO

I would like to use MERGE to merge 2 tables from different databases into one table. Both Databases are on the same server.

Currently, I can do the following:

USE Northwind2

SELECT a.CategoryID
FROM Northwind.dbo.Categories a
INNER JOIN Northwind2.dbo.Categories b ON a.CategoryID = b.CategoryID


However, I need to merge the results into one table. I thought I could do something like this:

USE Northwind2

MERGE INTO Categories B
USING (
SELECT E.CategoryID
FROM Northwind.dbo.Categories) E
ON (B.CategoryID = E.CategoryID)
WHEN MATCHED THEN
//update the table
WHEN NOT MATCHED THEN
//insert into the table


This will return an error saying the following:

Msg 10739, Level 15, State 1, Line 10
The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.
​

I'm not sure how I would remove the multi-part identifiers and still have this work... since I need to define which database we are looking in.

Any ideas on how I would get around this? Any help would be appreciated

Continue reading...