import query

[beechill4]

Hi,

I have a test database which has the same tables as my production database.

Some data in the test tables is the same as the data in the production database, but both test and production have data which the other does nt have.

What is the best way to copy the production data into test but to hold onto the test data which is not in production?

Reagrds

 

[SantaMufasa]

I recommend the following scenario (presuming that you can "tnsping" the production database from the test database). Do all of the following while logged in as the TEST schema user:

1) Create a database link on the test database to the production database:

create database link MY_PROD
       connect to <prod_schema_name>
       identified by <prod_schema_password>
       using '<TNS alias for PROD instance>';

2) Create a table of consolidated information from both the PROD table and the TEST table:

CREATE TABLE TEST_HOLD AS
    SELECT * from <table_name>@MY_PROD
    UNION
    SELECT * from <table_name>;

3) Empty out the TEST table:

TRUNCATE TABLE <table_name>;

4) Load the consolidated information into the TEST table:

INSERT INTO <table_name> SELECT * from TEST_HOLD;
COMMIT;

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]