Snowflake how to split a field having values separated with commas and add them separately to the target table each value in a single row?

I have a field that might hold values as follows:

field_val = ‘Val1, Val2’;

So using merge into command, I need to split these values and add them separately each as a single row.

MERGE INTO target_table TT
    USING (SELECT FIELD_VAL, ID FROM SOURCE_TABLE) ST
    ON (TT.ID = ST.ID)
    WHEN NOT MATCHED THEN
    INSERT (ID, FIELD_VAL) VALUES (SOME_ID, ST.FIELD_VAL)

In that case, I don’t want them added row to be: 23, 'Val1, Val2'

What I want is to add the separately as single rows:

23, 'Val1'

24, 'Val2'

Answer

You can use LATERAL SPLIT_TO_TABLE to split the array to a table, but I don’t think that you can add them with different IDs.

create or replace table target_Table (id number, v varchar);

merge into target_Table t
using (
with source_table as (select 0 id, 'Val1, Val2' f )
select id, trim(value) nv from source_table, 
lateral SPLIT_TO_TABLE(f,',')
) s on t.id = s.id
when not matched then
insert (id, v) values ( id, nv  );

select * from target_table;

+----+------+
| ID |  V   |
+----+------+
|  0 | Val1 |
|  0 | Val2 |
+----+------+