maltman maltman - 21 days ago 14
SQL Question

Insert into SQL from 2 tables into 1 where some values exists between the two

Sorry the title might be confusing but I will try to explain better here. So I have 2 tables.

Table1
Name
Int
Decimal

Table2
Name
Int
Decimal


I am trying to combine the data into a third table. It would look something like this

Table3
Name
Table1_Int
Table1_Decimal
Table2_Int
Table2_Decimal


The insert is not an issue. The issue is I have a few names that exist in one table and not the other. I still want these to show up, just with NULL values where there are no values.

Here is my stored proc

IF NOT EXISTS(
SELECT Name FROM Table3)
INSERT INTO Table3(
Name,
Table1_Int,
Table1_Decimal,
Table2_Int,
Table2_Decimal)
SELECT
t.Name,
AVG(t.Int) as Table1_Int,
AVG(CAST(t.Decimal as decimal(6,2))) as Table1_Decimal
AVG(a.Int) as Table2_Int,
AVG(CAST(a.Decimal as decimal(6,2))) as Table2_Decimal
FROM Table1 t
JOIN Table2 a
ON t.Name = a.Name
GROUP BY t.Name
ELSE
UPDATE Table3
SET Name = Name


Is there anyway I can grab all names, no matter if they match between tables?

Answer

I'd go with the basic logic of a FULL OUTER JOIN. I'd expect it to look something like this;

SELECT
COALESCE(t.name, a.name) name
,AVG(t.Int) t_int
,AVG(CAST(t.Decimal as decimal(6,2))) t_decimal
,AVG(a.Int) a_int
,AVG(CAST(a.Decimal as decimal(6,2))) a_decimal
FROM Table1 t
FULL OUTER JOIN Table2 a
    ON t.name = a.name
GROUP BY COALESCE(t.name, a.name)
Comments