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.

asked 26 Feb, 16:15

Rolle's gravatar image

Rolle
492343650
accept rate: 0%

Comment Text Removed

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?

(27 Feb, 03:25) Volker Barth

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
permanent link

answered 27 Feb, 03:29

Christian%20Hamers's gravatar image

Christian Ha...
39681022
accept rate: 50%

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, 03:39) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×95

question asked: 26 Feb, 16:15

question was seen: 88 times

last updated: 27 Feb, 03:39