合并来自不同数据库的 2 个表

问题描述

假设我想将来自不同数据库的 2 个表合并到一个表中，其中包含来自 2 个表的所有数据:

结果看起来像这样:

结果表中的条目是不是多余的，因为保时捷和大众有2个条目?或者我可以只在股票"列中添加值，因为标记"列是明确的?

1. 您需要创建到另一个数据库的数据库链接这里是如何创建数据库链接的示例 http://psoug.org/definition/create_database_link.htm从另一个数据库创建选择语句后应该看起来:select * from tableA@"database_link_name"
2. 然后您需要使用 MERGE 语句从另一个数据库推送数据，因此合并语句应如下所示.

3. 您可以在此处阅读合并语句:https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

   <前><代码>合并到 result_table res使用(选择标记、库存、some_unique_id来自 result_table res2联合所有选择标记、库存、some_unique_id来自 tableA@"database_link_name") diff在 (res.some_unique_id = diff.some_unique_id )当匹配然后更新集 res.mark = diff.mark,res.stock = diff.stock当不匹配时插入(res.mark,资源库存，res.some_unique_id)价值观(差异标记，差异股票，diff.some_unique_id);

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. 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);
   

这篇关于合并来自不同数据库的 2 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！