Jesus Oliva Jesus Oliva - 3 months ago 9
SQL Question

Insert splitted values from comma separated list

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

Answer

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.

Comments