Copying data between Oracle schemas using SQL

sql oracle insert oracle10g bulkinsert
chabzjo picture chabzjo · May 15, 2009 · Viewed 40.6k times · Source

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?

Answer

Sliff picture Sliff · May 15, 2009

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.

Related questions

Oracle SQL: Use sequence in insert with Select Statement

Basically I want to run the following query: INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count) SELECT my_seq.nextval, '2010', '12', 'ALL', 'ALL', region, sum(avg_msrp * count) / …

Read More
Oracle sql merge to insert and delete but not update

Is there a way to use oracle merge to insert and delete but not update? I have a table representing a set of values related to a single row in another table. I could change the set of values by …

Read More
Creating a CTE in Oracle

I am trying to create a CTE in Oracle that doesn't select from an existing table but instead has data inserted into it. Currently, I am creating a table and then dropping it after the query is done. Is there …

Read More