CODEWITHSUNDEEP
sqldatabaseoracle-databaseinsert
PepeDeLew
PepeDeLew

Reputation: 346

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:

Table A

ID      Value
1       ABCDEFGHJKLM
2       NOPRST
3       NULL VALUE

I want to insert these values into table B like this format

Table B

ID     Value1       Value2       Value3
1       ABC          D            EF
1       GHJ          K            LM
2       NOP          R            ST

Upvotes: 5

Views: 3137

Answers (3)

Florin Ghita
Florin Ghita

Reputation: 17643

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;

see Sqlfiddle.

Upvotes: 6

podiluska
podiluska

Reputation: 51514

select ID,
    SUBSTR(value,number*6+1,3),
    SUBSTR(value,number*6+4,1),
    SUBSTR(value,number*6+5,2)
from yourtable,
    (select 0  as number union select 1 union select 2 union select 3 union select 4 
            union  select 5 union select 6) as numbers 
      /* etc up to the max length of your string /6 */
where LEN(value)>number*6

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24096

try this:

Please convert it to ORACLE SQL.. Even though, its using a while loop, its doing bulk inserts..and the loop is executed as per the length of maximun length of value in the table

declare @max_len int=0;
declare @counter int=0;
declare @col_index int=1;
select @max_len=MAX(len(Value)) from TableA

while (@max_len/6 > @counter)
begin
    set @counter=@counter+1

    Insert into TableB

    select ID,substring(Value,@col_index,3),
              substring(Value,@col_index+3,1),
              substring(Value,@col_index+4,2) 
    from TableA where substring(Value,@col_index,3) is not null

    set @col_index=@col_index+6
end

Upvotes: 0

Related Questions