Copying data between Oracle schemas using SQL

Question

I'm trying to copy data from one Oracle schema (CORE_DATA) into another (MY_DATA) using an INSERT INTO (...) SQL statement.

What would the SQL statement look like?

Solution 1

Prefix your table names with the schema names when logged in as a user with access to both:

insert into MY_DATA.table_name select * from CORE_DATA.table_name;

Assuming that the tables are defined identically in both schemas, the above will copy all records from the table named table_name in CORE_DATA to the table named table_name in MY_DATA.

Solution 2

usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel]

  [db]   : database schema string, e.g., grprass/grprass@grprass, pplan/pplan@prassm1
  [opt]  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
  [table]: name of the destination table
  [cols] : a comma-separated list of destination column aliases ( optional )
  [sel]  : any valid SQL SELECT statement

SQL> COPY FROM scott/tiger@schema1 TO scott/tiger@schema2 insert mytable using select * from mytable;

Solution 3

your schema must have grant create any table privilege for this

Comments

[Owen]

Also need to make sure your user has select grants on CORE_DATE.table_name. I assume your user has the proper grants on MY_DATE.table_name

[Admin]

you should know that the above solution Works but constraints won't be copied

[KyleG]

Thanks. I was just looking to move the data anyway.