How to insert multiple rows from one column?

Question

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

What is the criteria of splitting the Value string of Tabale A? @mehmet — TechDo
– TechDo, Commented Sep 7, 2012 at 11:00
It 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. @techdo — PepeDeLew
– PepeDeLew, Commented Sep 7, 2012 at 11:02

Florin Ghita · Accepted Answer · 2012-09-07 16:49:45Z

6

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.

edited Sep 7, 2012 at 16:49

answered Sep 7, 2012 at 11:07

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PepeDeLew Over a year ago

Hi, I think it makes almost what I want but in my example for row 2 in Table A. This code adds also one empty row for next 6 chars. Where can I check If null control in this code?

PepeDeLew Over a year ago

Ok thanks a lot, I add where clause length(trim(substr(value,n*6+1,3))) > 0 at the end of the query. And Now I get the exact result.

Florin Ghita Over a year ago

I think something is wrong with your data. I've tested on sqlfiddle. See the edit.

PepeDeLew Over a year ago

Yes, I see your edit. My Table A is also a select query, maybe it causes null values. Thanks Again.

podiluska · Accepted Answer · 2012-09-07 11:21:09Z

3

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

answered Sep 7, 2012 at 11:21

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-09-07 11:17:33Z

0

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

answered Sep 7, 2012 at 11:17

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

Collectives™ on Stack Overflow

How to insert multiple rows from one column?

Table A

Table B

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table A

Table B

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related