Huge table copy avoid duplicates

bulk-insertbulkcopyoracle-11g-r2

I've a table with more than 10 billion records. Many NEW records (in quantum of 40-50 million records)are added regularly in a file as input part of batch process at night. The active table is majorly used for read operations throughout the day. To facilitate fast insert we keep active and inactive table. Everyday we first wipe out the data from inactive table, copy the existing data from active table to inactive table and insert new records. Lastly switch the inactive table to make it active.

I need to know how can I avoid duplicates records existing records to be copied from active table and in NEW records while copying.
eg.

transaction_data_active
customer_num, help_number, issue_number issue_details, issue_closed_status

transaction_data_inactive
customer_num, help_number, issue_number, issue_details, issue_closed_status

The table doesn't have partitions and supports global index on customer_num, help_number, issue_number columns. We are using oracle 11g

Best Answer

I am not an Oracle expert, but I can answer from DB2 perspective, you may look for similar features/commands in Oracle. In DB2, you can achieve this by:

Using Import command with INSERT_UPDATE option.
You also have a choice to use MERGE statement to merge data between two tables.

Hope it helps you.

Related Solutions

Sql-server – Copy huge amount from 1 table to other

If you want the second table to contain the same data as the first table, why not simply wipe the table out and replace it?

BEGIN TRANSACTION;

  DROP TABLE TransactionEntry;

  SELECT * INTO dbo.TransactionEntry FROM dbo.TransactionEntryN
    -- other WHERE clauses?
  ;

COMMIT TRANSACTION;
-- create indexes / permissions etc.

Sql-server – What options exist for copying data between servers on a regular basis

I'd consider backup/restore of a mix of full and differential backups from prod to dev. Then synch between this restored copy to your actual dev databases

SQL Server 2008 R2+ supports backup compression in Standard Edition (In SQL Server 2008 it was Enterprise Edition only) which makes this easier.

Reasons:

You test your backup integrity and restore capability
You can reset your dev database easily if some code monkey bollixes it
You can compare "before" and "after" DDL, performance etc
You can snapshot the restored database as needed to roll this back if you make changes

I've used this before and will use it again for these reasons

I suggested this in my answer to The smallest backup possible ... with SQL Server

Best Answer

Related Solutions

Sql-server – Copy huge amount from 1 table to other

Sql-server – What options exist for copying data between servers on a regular basis

Related Question