Lajos Arpad - 9 months ago 36

SQL Question

Let us suppose that I need to

`insert`

`t1.c1`

`insert into t1(c1, c2, c3)`

values

('a', 'c2val', 'c3val'),

('b', 'c2val', 'c3val'),

('c', 'c2val', 'c3val'),

('d', 'c2val', 'c3val'),

('e', 'c2val', 'c3val'),

('f', 'c2val', 'c3val'),

('g', 'c2val', 'c3val'),

('h', 'c2val', 'c3val');

However, if I intend to

`insert`

`table`

`insert`

`select`

`insert into t1(c1, c2, c3)`

select ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

Instead, I have(

`insert into t1(c1, c2, c3)`

select 'a' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'b' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'c' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'd' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'e' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'f' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'g' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

insert into t1(c1, c2, c3)

select 'h' as c1, t2.c2, t2.c3

from t2

where t2.c2 > t2.c3;

I had to write a LOT of this kind of stuff today and I wonder whether I could have done better with using the values as a set instead of writing an

`insert`

`select`

EDIT:

'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h' is not my actual data, which is in fact quite ugly and random. The letters were used solely as an example and we should not rely on their particular attributes.

Answer

not really sure what the issue is, would this work?

```
insert into t1(c1, c2, c3)
select c1, c2, c3
from
(select
c2, c3
from
t2
where c2 > c3) cross join
(select 'a' as c1 union
select 'b' union
select 'c' union
select 'd' union
select 'e' union
select 'f' union
select 'g' union
select 'h' ) a
```

are you asking how to create a set given a bunch of ugly and random values? If so, you can either put them in a table or just `union`

them together.

Source (Stackoverflow)