Lajos Arpad Lajos Arpad - 1 month ago 8
SQL Question

How to insert a set to a table?

Let us suppose that I need to

insert
'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h' as values of
t1.c1
. This is a possibility:

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
these into the
table
using an
insert
-
select
, then this will throw a syntax error:

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(?) to do it individually:

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
for each individual value.

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.