Home » SQL & PL/SQL » SQL & PL/SQL » data insert problem
data insert problem [message #286018] Thu, 06 December 2007 05:29 Go to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi All

I am facing a problem while inserting the data in a table.
table T1 is looking like this

col1-------------------- col2-----------col3------col4
1;2;3;4;5 --------------- x ------------ y -----a;b;c;d;e

i want to insert the data in table T2 like this.

1 x y a
2 x y b
3 x y c
4 x y d
5 x y e

please help me to find the solution
Thanks in advance

Nusrat

[Updated on: Thu, 06 December 2007 05:36]

Report message to a moderator

Re: data insert problem [message #286041 is a reply to message #286018] Thu, 06 December 2007 06:17 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

CREATE TABLE T_11
(
  COL1  VARCHAR2(10 BYTE),
  COL2  VARCHAR2(10 BYTE),
  COL3  VARCHAR2(10 BYTE),
  COL4  VARCHAR2(10 BYTE)
);

CREATE TABLE T_22
(
  COL1  VARCHAR2(10 BYTE),
  COL2  VARCHAR2(10 BYTE),
  COL3  VARCHAR2(10 BYTE),
  COL4  VARCHAR2(10 BYTE)
);

INSERT INTO T_11 ( COL1, COL2, COL3, COL4 ) VALUES ( 
'1;2;3;4;5', 'x', 'y', 'a;b;c;d;e'); 
COMMIT;


insert into t_22(col1,col2,col3,col4) 
 select substr(replace(col1,';'),1,1),col2,col3,substr(replace(col4,';'),1,1) from t_11;
insert into t_22(col1,col2,col3,col4) 
 select substr(replace(col1,';'),2,1),col2,col3,substr(replace(col4,';'),2,1) from t_11;
insert into t_22(col1,col2,col3,col4) 
 select substr(replace(col1,';'),3,1),col2,col3,substr(replace(col4,';'),3,1) from t_11;
insert into t_22(col1,col2,col3,col4) 
 select substr(replace(col1,';'),4,1),col2,col3,substr(replace(col4,';'),4,1) from t_11;
insert into t_22(col1,col2,col3,col4)
 select substr(replace(col1,';'),5,1),col2,col3,substr(replace(col4,';'),5,1) from t_11;
 commit;


Specific for this example.

Regards,
Kiran.

[Updated on: Thu, 06 December 2007 06:20]

Report message to a moderator

Re: data insert problem [message #286045 is a reply to message #286041] Thu, 06 December 2007 06:22 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi Kiran

thanks for this response .
if i have more than 1oo record and of different data length then i will not be able to insert the data .

if i have data like this then it will be problem for me

col1-------------------- col2-----------col3------col4
123;2t;3;4qa;5 --------- x ------------ y -----apo;bfr;ct;d;e

data insertion should be dynamic

please give some other solution

Thanks
Nusrat

[Updated on: Thu, 06 December 2007 06:24]

Report message to a moderator

Re: data insert problem [message #286047 is a reply to message #286045] Thu, 06 December 2007 06:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have a look at str2tbl examples on Ask Tom.

They might give you some idea.

Also, define what should happen when the source data is like:

col1       col2   col3   col4
A;B;C      Y;X    V      1;2;3;4;5
D;E;F;G;H  Y;X    P      1;2


And use CODE tags.
Re: data insert problem [message #286053 is a reply to message #286018] Thu, 06 December 2007 06:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This link may give you the kick start you needed .

Thumbs Up
Rajuvan.

[Updated on: Thu, 06 December 2007 06:40]

Report message to a moderator

Re: data insert problem [message #286061 is a reply to message #286018] Thu, 06 December 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t1;
COL1                 COL2                 COL3                 COL4
-------------------- -------------------- -------------------- --------------------
1;2;3;4;5            x                    y                    a;b;c;d;e
123;2t;3;4qa;5       x                    y                    apo;bfr;ct;d;e

2 rows selected.

SQL> with lines as (select level line from dual connect by level <= 10)
  2  select substr(col1, 
  3                instr(';'||col1||';', ';', 1, line),
  4                instr(';'||col1||';', ';', 1, line+1)
  5                - instr(';'||col1||';', ';', 1, line) - 1) col1,
  6         col2, col3, 
  7         substr(col4, 
  8                instr(';'||col4||';', ';', 1, line),
  9                instr(';'||col4||';', ';', 1, line+1)
 10                - instr(';'||col4||';', ';', 1, line) - 1) col4
 11  from t1, lines
 12  where line <= length(col1)-length(replace(col1,';'))+1
 13  order by 1
 14  /
COL1                 COL2                 COL3                 COL4
-------------------- -------------------- -------------------- --------------------
1                    x                    y                    a
123                  x                    y                    apo
2                    x                    y                    b
2t                   x                    y                    bfr
3                    x                    y                    c
3                    x                    y                    ct
4                    x                    y                    d
4qa                  x                    y                    d
5                    x                    y                    e
5                    x                    y                    e

10 rows selected.

Regards
Michel
Re: data insert problem [message #286069 is a reply to message #286018] Thu, 06 December 2007 07:08 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Excelent solution. Thumbs Up

Kiran.
Re: data insert problem [message #286079 is a reply to message #286018] Thu, 06 December 2007 07:20 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

But here

 where line <= length(col1)-length(replace(col1,';'))+1


If the col1 not having value '5' and Col4 has value 'e', then the last
5             x             y                    e

row will not be selected.

But, good one Smile
Re: data insert problem [message #286085 is a reply to message #286018] Thu, 06 December 2007 07:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then

line <= GREATEST(length(col1)-length(replace(col1,';')),
                 length(col2)-length(replace(col2,';'))) +1


My earlier link was having other alternatives including Michel's solution

Thumbs Up
Rajuvan.
Re: data insert problem [message #286086 is a reply to message #286079] Thu, 06 December 2007 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this a case in OP's problem?

Regards
Michel
Re: data insert problem [message #286553 is a reply to message #286086] Sat, 08 December 2007 03:44 Go to previous message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Thanks to all Smile

Above solution is working very well

Thanks again.
Nusrat
Previous Topic: Check if a column is NULL
Next Topic: Trigger with error on insert
Goto Forum:
  


Current Time: Thu Dec 08 03:56:44 CST 2016

Total time taken to generate the page: 0.25877 seconds