Insert splitted values from comma separated list

Question

I want to insert the values in a column from one table to another but the values of the first table are stored in a single string, separated by commas. I have to insert all values automatically without specifying the id, hence creating the following.

TABLE_1

ID_TRA | COND
------------
100    | 1,2,4
101    | 4
102    | 1, 16  <--- TRIM SPACE

TABLE_2
ID | ID_TRA | COND_ID
---------------------
1  | 100    | 1
2  | 100    | 2
3  | 100    | 4
4  | 101    | 4
5  | 102    | 1
6  | 102    | 16

Note

The id needs to be auto incremented as it's being inserted

Never, ever store data as comma separated values. It will only cause you lots of trouble. — jarlh, Aug 09 '16 at 10:51
@jarlh - It happens everywhere and all the time though. Very often it's legacy - from times before RMDB's even existed. Large organization, especially, refuse to spend the time and money to fix their data model. — , Aug 09 '16 at 11:08
Possible duplicate of [Splitting comma separated values in Oracle](http://stackoverflow.com/questions/28271744/splitting-comma-separated-values-in-oracle) — , Aug 09 '16 at 11:17

score 1 · Accepted Answer · edited Sep 14 '17 at 14:23

1

You can use an insert-select this way:

insert into table_2(ID, ID_TRA, COND_ID)
SELECT ROWNUM, ID_TRA, COND_ID
FROM (
        SELECT ID_TRA,
               trim (' ' from REGEXP_SUBSTR( COND, '([^,]*)(,|$)', 1, LEVEL, NULL, 1 ) ) AS COND_ID
        FROM   table_1 t
        CONNECT BY
               ID_TRA = PRIOR ID_TRA
        AND    PRIOR SYS_GUID() IS NOT NULL
        AND    LEVEL < REGEXP_COUNT( COND, '([^,]*)(,|$)' )
        ORDER BY 1, 2
        )

The ordering is important to decide how to assing the ID in your table; you can edit it by modifying the ORDER BY. The way to split the strings is taken from SO documentation.

If you need to add new rows to the table_2, not populating it from scratch, you should better use a sequence; the solution with rownum only works if the target table is empty.

edited Sep 14 '17 at 14:23

MT0

143,790
11
59
117

answered Aug 09 '16 at 11:11

Aleksej

22,443
5
33
38

Thanks for all friend. – BBoyOliva Aug 09 '16 at 12:06
If i put sequence on select stament not work? insert into table_2(ID, ID_TRA, COND_ID) SELECT MY_SQ.nextval, ID_TRA, COND_ID FROM ( SELECT ID_TRA, trim (' ' from REGEXP_SUBSTR( COND, '([^,]*)(,|$)', 1, LEVEL, NULL, 1 ) ) AS COND_ID FROM table_1 t CONNECT BY ID_TRA = PRIOR ID_TRA AND PRIOR SYS_GUID() IS NOT NULL AND LEVEL < REGEXP_COUNT( COND, '([^,]*)(,|$)' ) ORDER BY 1, 2 ) – BBoyOliva Aug 09 '16 at 12:18
If you want a sequence, simply use your_sequence.nextVal instead of ROWNUM in the external query – Aleksej Aug 09 '16 at 12:22

score 0 · Answer 2 · answered Aug 09 '16 at 11:24

Try this :

insert into table_2(ID, ID_TRA, cond)
select rownum,id_tra,trim(regexp_substr(t.cond, '[^,]+', 1, levels.column_value))  as prjcd from table_1 t,
table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.cond, '[^,]+'))  + 1) as sys.OdciNumberList)) levels;

Insert splitted values from comma separated list

2 Answers2