Merge 2 tables from different databases no matching fields into a temp table on SQL Server

Question

I'm working on a project and I'm stumped. I have 2 websites that take orders. The databases are on the same server. Each website stores the order data into tables in separate databases. The primary site uses "dbo.Orders", which is the table that is used to give data to our aspdotnetstorefront backend. Our sales people use this interface to review orders. The secondary site uses"dbo.orders", which is the table used to store orders. The second site's orders are not accessible on the aspdotnetstorefront. My job is to merge the orders from the secondary site into the table for the primary site so the orders can be seen on the aspdotnetstorefront backend like the orders from the primary site. I also have to create an identifier so when viewed the sales people will know which site the order was from. I think creating a temporary table and maybe an outer join or merge? Problem is the tables have no matching fields. Any suggestions would be appreciated.

Answer 1

You can use a UNION ALL to combine the records

SELECT 1 as SiteID, 
       * -- common fields
FROM   Database1.dbo.Orders
UNION ALL 
SELECT 2 as SiteID, 
       * -- common fields
FROM   Database2.dbo.Orders

Fields in Database2.dbo.Orders will have to be the same type as the Field in Database2.dbo.Orders. For example, the following will not work.

SELECT 1 as SiteID,
       CustomerName -- VARCHAR
UNION ALL
SELECT 2 as SiteID,
       OrderDate    -- DATETIME

Add fillers if you need to, if a column exists only in one table and you need to include it in the query.

SELECT 1 as SiteID,
       CustomerName as CustomerName,
       NULL as OrderDate  -- filler to match OrderDate
FROM   Database1.dbo.Orders
UNION ALL
SELECT 2 as SiteID,
       '',  -- filler to match CustomerName
       OrderDate
FROM   Database1.dbo.Orders

To create a temp table you can add the INTO [TABLE] syntax to the first select. Also example using your OrderNumber ordId fields

SELECT 1 as SiteID, 
       OrderNumber
INTO   #temp -- create temp table to hold all rows generated from union
FROM   Database1.dbo.Orders
UNION ALL 
SELECT 2 as SiteID, 
       ordId  -- will appear in second column [OrderNumber]
FROM   Database2.dbo.Orders