Rogerio Camorim Rogerio Camorim - 2 months ago 6
SQL Question

SQL Insert multiple row with only one varying data

I wanna insert muliples rows with a single insert, but only the last data is mutable from a list.
Like this.

INSERT INTO TB_TEMPLATE (
ID_BENEFICIO,ID_PROGRAMA,DDD
)VALUES ('42','78', X)


But the value from X is a list like

11,15,17,19,24,43,54,65


Can be done with a single insert?

All datas are String.

Answer

Something like this will work, assuming you are hard-coding the X value (and assuming the values are NUMBER, so they must be converted to VARCHAR2 before insertion - this is done with to_char()). Adapt as needed. If the list already exists as a column in a table, select that column from the table instead of "union all" from dual.

INSERT INTO TB_TEMPLATE (
    ID_BENEFICIO,ID_PROGRAMA,DDD
)
with 
     value_list ( x ) as (
       select 11 from dual union all
       select 15 from dual union all
       select 17 from dual union all
       select 19 from dual             -- etc.
     )
select '42', '78', to_char(x)
from   value_list
;
Comments