|
I have this table. (tbl1) ID Value Type Fieldumber 1 E2 10 10 2 A1 10 20 3 B2 10 30 3 B3 10 40 3 B4 10 50 4 G6 10 40 4 G7 10 50 4 G8 20 60 4 G0 10 60 4 G9 10 70 I have another table (tbl2) with two columns with many rows like this Value FieldNumber AA 100 BB 110 AQ 120 I want to add the seconnd table to the first table like this with sql: ID Value Type Fieldumber 1 E2 10 10 2 A1 10 20 3 B2 10 30 3 B3 10 40 3 B4 10 50 4 G6 10 40 4 G7 10 50 4 G8 20 60 4 G0 10 60 4 G9 10 70 1 AA 10 100 2 AA 10 100 3 AA 10 100 4 AA 10 100 4 AA 20 100 1 BB 10 110 2 BB 10 110 3 BB 10 110 4 BB 10 110 4 BB 20 110 1 AQ 10 120 2 AQ 10 120 3 AQ 10 120 4 AQ 10 120 4 AQ 20 120 Thought to use cross join or cross apply, but I don't get it working. |
|
I think this is what you are looking for insert into tbl1 (ID, Value, Type, FieldNumber)
select t.Id, tbl2.Value, t.Type, tbl2.FieldNumber
from tbl2,
(select distinct tbl1.Id, tbl1.Type
from tbl1) as t
FWIW, I would prefer a CROSS JOIN syntax: insert into tbl1 (ID, Value, Type, FieldNumber)
select t.ID, tbl2.Value, t.Type, tbl2.FieldNumber
from tbl2
cross join (select distinct tbl1.ID, tbl1.Type
from tbl1) t
"FieldNumber" seems more fitting then the original name:)
(27 Feb '19, 03:39)
Volker Barth
|
Do you want to get a result set containing these rows (aka doing a SELECT), or do you want to insert the values from tbl2 into tbl1 (i.e. doing an INSERT), so tbl1 contains the mentioned contents?