CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

merging two tables and adding additional column

I am using sql-server. I have two tables (simple snap shot below).

 table hlds                table bench
 name  country  wgt        name  country  wgt
 abc   us       30         abc   us       40
 mno   uk       50         ppp   fr       45
 xyz   us       20         xyz   us       15

what I would like to do is calculate the differnces in the wgt columns and insert the results into another table, lets call it merge_tbl. The other thing I would like to do is in merge_tbl have a bit column where it is 1 if the company exists in the table hlds.

So I would like the result to look like below,

 merge_tbl
 name  country  wgt  inHld
 abc   us       -10  1
 mno   uk       50   1
 xzy   us       5    1
 ppp   fr       -45  0

How do I go about doing this?

Upvotes: 1

Views: 42

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

I think you need a FULL OUTER JOIN to get records from both tables. Then, you can use a INSERT INTO SELECT statement to do the insert:

INSERT INTO merge_tbl
SELECT COALESCE(h.name, b.name) AS name, 
       COALESCE(h.country, b.country) AS country, 
       COALESCE(h.wgt, 0) - COALESCE(b.wgt, 0) AS wgt,
       CASE WHEN h.name IS NOT NULL THEN 1
            ELSE 0
       END AS inHld
FROM hlds AS h
FULL OUTER JOIN bench AS b ON h.name = b.name AND h.country = b.country

The ON clause of the JOIN operation depends on your actual requirements. I have made the assumption that records from hlds, bench tables match if both name and country fields are equal.

Demo here

Upvotes: 1

Related Questions