Analytics Software

try:

insert into <table_with_no_data> select * from <table_with_data>;

Yes Chaitanya thats correct, example:

SQL> create table emp_j as select * from emp where 1=2;

Table created.

SQL> desc emp_j;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPID                                              NUMBER
 EMPNAME                                            VARCHAR2(20)
 AGE                                                NUMBER
 SALARY                                             NUMBER
 DEPTID                                             NUMBER
 MGRID                                              NUMBER

SQL> select count(*) from emp;

  COUNT(*)
----------
        11

SQL> select count(*) from emp_j;

  COUNT(*)
----------
         0

SQL> insert into emp_j select * from emp;

11 rows created.

SQL> select * from emp;

     EMPID EMPNAME                     AGE     SALARY     DEPTID      MGRID
---------- -------------------- ---------- ---------- ---------- ----------
        34 TEST                         87    3453453          4
         1 RAMESH                       32      35000
         2 VIKAS                        23      22000
         3 RAJ                          35     500000          4          1
         4 RAKESH                       25      29000          1
         5 VIJAY                        30      31000          3
         6 KASHYAP                      28      28000          6
         7 TOM                          26      25000          5
         8 MAHESH                       24      22000          4
         9 PREM                         27      27000
        10 SRINIVAS                     42      54000          3

11 rows selected.

well .. i hv tried this .. it works but why 1=2 at the end..the statement works even without this..

Because that create table command is just to create table without copying data.
I think in your question, you said you want to copy data from another table with same structure. Thus why Jith created an empty table with the same structure.

HTH
SHY

beware of constraints while creating the copy table.
creation will create only NOT NULL constraints. primary key, foreing key and unique constraints will not be created with this query:"create table copy_trial as select * from trial;"

Dear Jith

I want to know will it possible to copy the data from another schema?
It may great help for me.
insert into emp_j select * from emp;

:>
Francis

By "copy data from schema", you mean table by table? Of course it is possible, if you have permissions on the other schema table.

insert into my_table
select * 
from other_schema.table

Cheers
Sarma.

Dear Sarma

How about copy data from one schema to another schema, which they are different Server(computer)?

Will it be possible?

Thanks a lot :>

Yes you can do it by create a db link (database link)
then write query like

insert into my_table
select *
from other_schema.table@(db link name)

Regards
asif

You need to have corresponding object privileges to do the same when you are trying to copy data from the table of another schema or another database.

Create a private database link in case if you want ti copy data from another database

With similar table structure you can copy data from one schema to another
schema
login to target database1


Condition1 Append data
copy from scott/tiger@database2 append emp using select * from emp;

Condition2 create new table
copy from scott/tiger@database2 create emp using select * from emp;