Friday, June 6, 2008

PL\SQL : Copy tables between databases

As you noticed, my posts run around my daily problems, and this isn't an exception. Past week I needed to copy large amount of data between production and development database to perform some tests. After some research I've found SQL*Plus COPY command, that fits like a glove in my needs.
So here's the syntax:

COPY FROM database TO database action
destination_table (column_name, column_name...) USING query

where action stands for:

  • create - if destination table doesn't exist yet;
  • replace - if destination table exists and we wish to drop and create it again;
  • append - Inserts data if table exist, otherwise it will be created;
  • insert - Insert data into an existing table.

An example could be :

SQL>copy from user/pass@DB1 
   to user2/pass2@DB2
   append new_emp
   using select * from emp;
Enjoy!
em

2 comments:

  1. ahmad seddehAugust 25, 2008 at 1:39 PM

    i think the copy tables using SQL make redundancy in row ids values and you must use EIM

    ReplyDelete
  2. GaussFebruary 4, 2010 at 9:53 AM

    This is at some point nice. I thought it could help me but it doesn't. It's similar to importing an sql file that carries INSERT statements and then I insert them on the other database.

    I get the same error with this method of yours: ORA-00001. Of course there should be ways to avoid this problem but since I don't know how, I was looking for other methods.

    A guy at my job show me how he can avoid this kind of error on our databases and imports the data using a "loader" option instead of creating a file with INSERTs. Then he edits the loader file a bit and run them with a command line tool "SQL Loader" tool I think, and it works!

    The problem is I don't still get it right, have to learn to use the loader.

    ReplyDelete