Merge 2 Tables from different Databases

问题:

Hypothetically I want to merge 2 tables from different databases into one table, which includes all the data from the 2 tables:

The result would look like something like this:

Aren't the entries in the result table redundant, because there are 2 entries with Porsche and VW? Or can I just add the values in the column 'stock' because the column 'Mark' is explicit?

回答1:

I hope this will help you

SELECT ROW_NUMBER() OVER (ORDER BY Mark) AS new_ID, Mark, SUM(Stock) AS Stock
FROM 
(
SELECT Mark,Stock FROM Database1.dbo.table1
UNION ALL
SELECT Mark,Stock FROM Database2.dbo.table2
) RESULT 
GROUP BY Mark

回答2:

Try this:

Select Mark, Stock, row_number() over(order by Mark desc) from table1 
union all
Select Mark, Stock, row_number() over(order by Mark desc) from table2

回答3:

regardless of the data redundancy, you could use union all clause to achieve this. Like:

Select * From tableA UNION ALL Select * From tanleB

Make sure the total number of columns and datatype should be matched between each

回答4:

Don't forget to use fully qualified table names as the tables are in different databases

SELECT
 Mark
,Stock
FROM Database1.dbo.table1

UNION ALL

SELECT
 Mark
,Stock
FROM Database2.dbo.table2

回答5:

If these are 2 live databases and you would need to constantly include rows from the 2 databases into your new database consider writing the table in your 3rd database as a view rather.

This way you can also add a column specifying which system the datarow is coming from. Summing the values is an option, however if you ever have a query regarding a incorrect summed value how would you know which system is the culprit?

回答6:

1. you need to create database link to another database here is the example on how to create database link http://psoug.org/definition/create_database_link.htm after creating your select statement from another database should look: select * from tableA@"database_link_name"
2. Then you need to use MERGE statement to push data from another database so the merge statement should look something like this.

3. you can read about merge statement here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

   
   merge into result_table res
   using (select mark, stock, some_unique_id
            from result_table res2
          union all
          select mark, stock, some_unique_id
            from tableA@"database_link_name") diff
   on (res.some_unique_id = diff.some_unique_id )
   when matched then
     update set res.mark = diff.mark, 
                  res.stock = diff.stock
   when not matched then
     insert
       (res.mark, 
            res.stock, 
            res.some_unique_id)
     values
       (diff.mark, 
            diff.stock, 
            diff.some_unique_id);