Import data from Table A into Table B

[Ablakios]

Hi

I am trying to import Data from table A to Table B in the same schema. but so far I see no docs about the related task?
Any suggestion?

[pando]

Hmm why you need an import for that?!?!

Isnt insert select very easy???

[DaPi]

Insert into tableB
select whatever from tableA
where . . . . . .

. . . but I've a sneeking feeling that's not what you want.

[Ablakios]

Thanks for the prompt response.
Here is the problem.

We are trying to do some reorg.

I moved most the tables into a new created LMT.

I have attempted to move a couple of tables and was getting ora- 00997
illigal use of LONG datatype.

So I created a new table with varchar2 (2000)in the new LMT and trying to get the data into it.


SQL> insert into Table B SELECT * FROM A;
Failed with *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

any other easy way and advise will be highly appreciated.
Thanks

[pando]

use copy but be careful with decimals

[Ablakios]

Pando
I thought sqlplus copy is from one database to another using sqlnet connection? I might be mistaken. Can you please send me an example?
Thanks

[eyen1]

You can use copy just reference the same tnsnames.ora entry.
Here is the documentation location: http://download-west.oracle.com/docs...apb.htm#634246


Here is the example in the documentation:

COPY FROM HR/your_password@BOSTONDB -
TO HR/your_password@BOSTONDB -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

I have done this is the past just be careful to run initial test on a smaller set of data before going for a larger set. Also the dashes are necessary to indicate next line.

[acapper]

About about doing a create table as select * from ???
---------------------------
SQL> create table capper as select 'GGGGG' col1, 1000.11 col2 from dual;

Table created.

SQL> select * from capper
2 ;

COL1 COL2
----- ----------
GGGGG 1000.11

SQL> create capper2 as select * from capper;
create capper2 as select * from capper
*
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL> create table capper2 as select * from capper;

Table created.

SQL> select * from capper2;

COL1 COL2
----- ----------
GGGGG 1000.11

SQL>
-----------------------------


Or you could export import the table.

regards,

Adam

[Ablakios]

eyen1
Thank you very much. it was successful.
Again Thanks