Geek asks and answers

How to insert multiple rows from one column?

I want to insert multiple rows from one column, dividing the column value. But I have to do this without cursors due to performance issues.

Each valueis divided into 6 character lengths. Then these values ​​are also divided into values ​​of lengths of 3, 1 and 2 characters to insert different columns in table B.

I think the giving 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
+5
source share
3 answers

Suppose that 600 (100 rows) are maximally valid:

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 .

+6
source 
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
+3
source

:

ORACLE SQL.. , while, ...

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
0
source

More articles:


All Articles