1 Reply Latest reply: Oct 23, 2010 7:57 AM by Hoek RSS

    Inserting data from one table to another table

    785031
      Hi,

      I have two tables,

      Version :Oracle 9.2.0.1.0

      TEST1:

      no   name


      TEST2:

      col1  col2

      no 1
      name arun

      Required outcome is

      TEST1:
      no  name

      1 arun
      Also this should be applicable for more than one value in the column col2 of table TEST2.

      ex:-
      TEST2:

      col1  col2
           
      no 1,2..n
      name arun,ajay..n

      Please give me an idea on how to work this.

      I used decode & pivot insert for this,but the result is a failure.

      SQL>INSERT INTO test1 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test2;

      SQL>
      no   name

      1 null
      null arun

      AND

      SQL> INSERT ALL
      2 INTO test22 VALUES(no)
      3 INTO test22 VALUES(name)
      4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
      INTO test22 VALUES(name)
      *
      ERROR at line 3:
      ORA-00904: "NAME": invalid identifier

      Please help me find an answer to this using SQL or PL/SQL.


      Regards,
      Raja sekhar.

      Edited by: V.RajaSekhar on Oct 23, 2010 12:11 AM
        • 1. Re: Inserting data from one table to another table
          Hoek
          Please help me find an answer to this using SQL or PL/SQL.
          Hi, it's always handy to post a testcase using CREATE TABLE + INSERT INTO statements, instead of describing data...
          Format your examples using the {noformat}
          {noformat} tags.
          See the FAQ for more information about tags: http://forums.oracle.com/forums/help.jspa
          More about why it matters how you ask a question: http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html
          
          Trying to translate your description to statements, you appeae to have the following situation:
          create table test1 (no number, name varchar2(50));
          create table test2 (col1 number, col2 varchar2(50));
          insert into test2(col1, col2) values (1, 'arun');
          insert into test2(col1, col2) values (2, 'ayay');
          ...and so on
          If that's the case than you can simply:
          SQL> select * from test1;

          no rows selected

          SQL> select * from test2;

          COL1 COL2
          ---------- --------------------------------------------------
          1 arun
          2 ayay

          SQL> insert into test1 (no, name)
          2 select col1, col2 from test2;

          2 rows created.

          SQL> select * from test1;

          NO NAME
          ---------- --------------------------------------------------
          1 arun
          2 ayay
          or use CTAS, when no objects depend on test1:
          SQL> drop table test1;

          Table dropped.

          SQL> create table test1 as select col1, col2 from test2;

          Table created.

          SQL> select * from test1;

          COL1 COL2
          ---------- ------------------------------------------------
          1 arun
          2 ayay