How to insert multiple rows from one column?
I want to insert multiple rows from one column by splitting column value. But I have to do that without cursors because of performance issues.
Every value is splitted to 6 chars length values. Then these values also splitted to 3, 1 and 2 chars length values to insert different columns in table B.
I think giving a sample will clarify my question:
ID Value 1 ABCDEFGHJKLM 2 NOPRST 3 NULL VALUE
I want to insert these values into table B like this format
ID Value1 Value2 Value3 1 ABC D EF 1 GHJ K LM 2 NOP R ST
Supposing 600(100 rows) as maximum length of value:
insert into tableB select id, substr(value,n*6+1,3), substr(value,n*6+4,1), substr(value,n*6+5,2) from tableA join (select level-1 as n from dual connect by level <= 100) on length(value) > n*6;