This discussion is archived
1 Reply Latest reply: Oct 23, 2010 5:57 AM by Hoek RSS

Inserting data from one table to another table

785031 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points