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@DB1Enjoy!
to user2/pass2@DB2
append new_emp
using select * from emp;
2 comments:
i think the copy tables using SQL make redundancy in row ids values and you must use EIM
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.
Post a Comment