How to combine 2 tables and insert values into a 3rd table

Question

I have 2 tables formatted the following ways listed below...

T1

ID   SUB_ID   NAME   NUM
123  ABC      TEST   5
456  XYZ      HELLO  10

T2

ID   SUB_ID   NAME   NUM  CAT  ACTY
123  ABC      TEST   5    sjq  h5h
456  XYZ      HELLO  10   hwl  888

I want to look at these 2 tables and match based on ID and SUB_ID and join them together so I get all the columns from T1 and those columns missing from T1 that are in T2

Both these tables contain thousands of rows but I simplified for this example.

This is the code I have tried but I am getting to many rows back..

SELECT * 
FROM T1 YY
INNER JOIN T2 ZZ
WHERE YY.ID = ZZ.ID and YY.SUB_ID = ZZ.SUB_ID

Any help on how to execute this would be GREATLY appreciated. THANKS!!

Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/insert-into-values-select-from) — MPelletier, Apr 10 '17 at 17:40
Hi Mark... unfortunately I do not know where to start. I tried an inner join but I am receiving more rows than I should be... let me know posting the inner join code I have would help — knight944, Apr 10 '17 at 17:45
Well, first, if you are getting too many rows, you're missing a condition. Why do you say you're getting too many rows? — MPelletier, Apr 10 '17 at 18:00
I should only get back 2 rows plus the 2 missing columns from T2 — knight944, Apr 10 '17 at 18:02
Let's start with rows. Do all the rows correspond to your inner join rules? (Let's do this sanity check). If they all respect your inner join rules, what makes you say some rows don't belong? What makes them not belong? — MPelletier, Apr 10 '17 at 18:05
Is ALL the data in the columns in table 2 duplicated from table 1? The data as shown indicates such and thus any query just adds more columns with duplicate data - same as an INNER JOIN would. — Mark Schultheiss, Apr 10 '17 at 18:06
I simplified my example but in reality I have thousands of rows in the 2 tables. the ID, SUB_ID, NAME will match from the 2 tables everything else will be different values — knight944, Apr 10 '17 at 18:21
If you are getting "too many" rows, that probably means either T1 or T2 have multiple rows with the same ID and SUB_ID. But we have no way of answering your question if we don't know what the data is like... — , Apr 11 '17 at 06:05

Mark Schultheiss · Answer 1 · 2017-04-10T18:37:38.117

1

I am going to go out on a "guess" here with (THE WHERE CLAUSE IS A GUESS ONLY)

INSERT INTO T3
 (ID, SUB_ID, NAME, NUM, CAT, ACTY)
SELECT T1.ID, T1.SUB_ID, T1.NAME, T1.NUM,
      T2,CAT,  T2.ACTY
FROM T1
INNER JOIN T2
    ON T1.ID = T2.ID 
       AND T1.SUB_ID = T2.SUB_ID
WHERE (T1.ID = 123 AND T1.SUB_ID = 'ABC')
   OR (T1.ID = 456 AND T1.SUB_ID = 'XYZ')

IF T3 has an identity on the ID you may have to alter query to account for that.

In reference to my comment this seems to be the same result:

INSERT INTO T3
 (ID, SUB_ID, NAME, NUM, CAT, ACTY)
SELECT T2.ID, T2.SUB_ID, T2.NAME, T2.NUM, T2,CAT, T2.ACTY
FROM T2
WHERE (T1.ID = 123 AND T1.SUB_ID = 'ABC')
   OR (T1.ID = 456 AND T1.SUB_ID = 'XYZ')

edited Apr 10 '17 at 18:37

answered Apr 10 '17 at 18:18

Mark Schultheiss

32,614
12
69
100

I DO fail to see really where this differs from simply selecting from T2 given the limited information we have to go on however - can we eliminate the JOIN and T1 altogether? – Mark Schultheiss Apr 10 '17 at 18:26
I can not delete any tables. I need to combine the 2 tables so that I have all the columns. We don't have to use a join but I need to have the missing columns from T2 that are not in T1 – knight944 Apr 10 '17 at 18:32

score 0 · Accepted Answer · answered Apr 12 '17 at 21:25

Your code (something similar to below) is correct, but may surprise you if one or the other tables has more than one row with the same ID and SUB_ID.

SELECT * 
FROM T1 YY
INNER JOIN T2 ZZ
WHERE (YY.ID = ZZ.ID AND YY.SUB_ID = ZZ.SUB_ID)

The following example records will return 4 rows, not 2, because each of the rows in the first table matches 2 rows in the second table.

T1

ID   SUB_ID   NAME   NUM
123  ABC      TEST   5
123  ABC      TEST2  10

T2

ID   SUB_ID   NAME   NUM  CAT  ACTY
123  ABC      TEST   5    sjq  h5h
123  ABC      TEST2  10   hwl  888

How to combine 2 tables and insert values into a 3rd table

2 Answers2