CODEWITHSUNDEEP
sqlsql-servert-sql
Hammelkeule
Hammelkeule

Reputation: 317

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:

enter image description here

The result would look like something like this:

enter image description here

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?

Upvotes: 3

Views: 5923

Answers (6)

TheName
TheName

Reputation: 733

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

Upvotes: 2

ThatChris
ThatChris

Reputation: 752

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?

Upvotes: 0

Ali Ashkar Ct
Ali Ashkar Ct

Reputation: 50

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

Upvotes: 1

HotblackDesiato
HotblackDesiato

Reputation: 348

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

Upvotes: 0

LONG
LONG

Reputation: 4620

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

Upvotes: 0

Ryan John
Ryan John

Reputation: 1430

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

Upvotes: 0

Related Questions