tranfer of data within tables

Expand Post »

i ve 1 table named 'A',it contains many attributes in 1 attribute the values are stored like this "Q108:5380;Q208:5380" this data present in table 'A' should be entered in table named 'B' in two fields named B1,B2 as
B1 B2
Q108 5380
Q208 5380

like this in two columns this data should be entered in table named 'B' from table 'A'. here : means separates two columns and ; means separates two rows.
please help me or tell me how to write this in code. i am using oracle database. thank you

palavi

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

58 posts
since Aug 2008

Permalink

Feb 16th, 2009

Re: tranfer of data within tables

here is some pl/sql code that gives you an idea I hope that would help

Oracle Syntax (Toggle Plain Text)
DECLARE
a1 VARCHAR2(255);
b1 VARCHAR2(255);
a2 VARCHAR2(255);
b2 VARCHAR2(255);
qry VARCHAR2(4000);
CURSOR cr_a IS (SELECT * FROM a);
BEGIN
  --loop goes to every row in a
  FOR rc_a IN cr_a
  LOOP
    --seperates column of A table to for variable
    --you have to write it it is simple string operations
    seperatecolumn(rc_a.COLUMN , a1,a2,b1,b2);
    qry := 'insert into b (B1,B2) values (' || a1 || ',' || a2 || ')';
    --run query
    EXECUTE IMMEDIATE qry;
    qry := 'insert into b (B1,B2) values (' || b1 || ',' || b2 || ')';
    EXECUTE IMMEDIATE qry;
  END LOOP;
END;
declare
a1 varchar2(255);
b1 varchar2(255);
a2 varchar2(255);
b2 varchar2(255);
qry varchar2(4000);
cursor cr_a is (select * from a);
begin
  --loop goes to every row in a
  for rc_a in cr_a
  loop
    --seperates column of A table to for variable
    --you have to write it it is simple string operations
    seperatecolumn(rc_a.column , a1,a2,b1,b2);
    qry := 'insert into b (B1,B2) values (' || a1 || ',' || a2 || ')';
    --run query
    execute immediate qry;
    qry := 'insert into b (B1,B2) values (' || b1 || ',' || b2 || ')';
    execute immediate qry;
  end loop;
end;

yilmazhuseyin

Reputation Points: 31

Solved Threads: 5

Light Poster

Offline

48 posts
since Oct 2006

Permalink

Feb 17th, 2009

Re: tranfer of data within tables

thank you very much it help me.

palavi

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

58 posts
since Aug 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: Cloud computing

Next Thread in Oracle Forum Timeline: Update using join

DaniWeb Message

Cancel Changes

User Name
Password