Geek Answers Handbook

Copying data between Oracle schemas using SQL

I am trying to copy data from one Oracle schema ( CORE_DATA ) to another ( MY_DATA ) using the INSERT INTO (...) SQL statement.

What does an SQL expression look like?

+7
sql oracle oracle10g insert bulkinsert
source share
3 answers

A prefix for table names with schema names when logging in as a user with access to both:

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

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

+17
source share
 usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel] [db] : database schema string, eg, 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;
+3
source share

your scheme should provide a grant to create any privilege table for this

+1
source share

More articles:


All Articles