問題描述

假設(shè)我想將來自不同數(shù)據(jù)庫的 2 個表合并到一個表中，其中包含來自 2 個表的所有數(shù)據(jù):

結(jié)果看起來像這樣:

結(jié)果表中的條目是不是多余的，因為保時捷和大眾有2個條目?或者我可以只在股票"列中添加值，因為標(biāo)記"列是明確的?

1. 您需要創(chuàng)建到另一個數(shù)據(jù)庫的數(shù)據(jù)庫鏈接這里是如何創(chuàng)建數(shù)據(jù)庫鏈接的示例 http://psoug.org/definition/create_database_link.htm從另一個數(shù)據(jù)庫創(chuàng)建選擇語句后應(yīng)該看起來:select * from tableA@"database_link_name"
2. 然后您需要使用 MERGE 語句從另一個數(shù)據(jù)庫推送數(shù)據(jù)，因此合并語句應(yīng)如下所示.

3. 您可以在此處閱讀合并語句:https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

   <前><代碼>合并到 result_table res使用(選擇標(biāo)記、庫存、some_unique_id來自 result_table res2聯(lián)合所有選擇標(biāo)記、庫存、some_unique_id來自 tableA@"database_link_name") diff在 (res.some_unique_id = diff.some_unique_id )當(dāng)匹配然后更新集 res.mark = diff.mark,res.stock = diff.stock當(dāng)不匹配時插入(res.mark,資源庫存，res.some_unique_id)價值觀(差異標(biāo)記，差異股票，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);
   

這篇關(guān)于合并來自不同數(shù)據(jù)庫的 2 個表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！