Add values from one table to another with sql

[former_SQLA_member1694869]

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.

[VolkerBarth]

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?

[Chris26 · Accepted solution]

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

[VolkerBarth · Accepted solution]

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