Updating missing records using 9 primary keys ?

[nvshah]

Can anybody give me answer on this ?

I have two same table names on different oracle databases (different servers also). Database1 has master table called HEADERS and Database2 has also table name called HEADERS. We are updating Database2.HEADERS using the Database1.HEADERS every night. It works fine but few records are missing on Database2.HEADERS. Both the table has 9 primary key columns (like composite primary key).

Which will be the better way to update the HEADERS table of Database2 from using Database1.HEADERS. Please advise asap.

REMEMBER I HAVE 9 PRIMARY KEYS. I HAVE TO USE ALL 9 KEYS & THAT'S HOW IT BECOMES 1 UNIQUE RECORD.

Thanks in advance.
NVS

 

[jee]

I think you need to provide more information. Exactly how are you updating now - that is what is the SQL you are using?

 

[nvshah]

There are two tables sitting on different oracle database. But their names are same called HEADERS. First database's name is CTR and second is CRP. The HEADER table of database CRP is a copy of table from HEADER@CTR. There are 9 primary keys in both the tables as they both are having same number of columns. Every night the procedure updates HEADER of CRP using insert statement based on create_date column.

But in my case I have to update HEADER of CRP for missing records. In HEADER of CTR there are 753141 records and in HEADER of CRP there are 752319 records. Both the table should have same number of records (rows).

The primary keys are:
COMPANY_ID, POSTING_CCY_ID, JOURNAL_ID, SOURCE_COMPANY_ID, TRANS_PREFIX, TRANS_NUM, TRANS_DUP_SEQ_NUM, RECORD_TYPE_IND, LINE_SEQ_NUM

I don't know how can I update missing records into HEADER of CRP database.

Thanks
Nikhil Shah

 

[Dagon]

If all you want to do is insert the missing records the syntax is:

insert into header@crp
select * from header@crt h1
where not exists
(select 'x' from header@crp h2
where h2.key1 = h1.key1
and h2.key1 = h2.key2
...
and h2.key9 = h2.key9)

 

[Dagon]

If all you want to do is insert the missing records the syntax is:

insert into header@crp
select * from header@crt h1
where not exists
(select 'x' from header@crp h2
where h2.key1 = h1.key1
and h2.key1 = h1.key2
...
and h2.key9 = h1.key9)

 

[Dagon]

If all you want to do is insert the missing records the syntax is:

insert into header@crp
select * from header@crt h1
where not exists
(select 'x' from header@crp h2
where h2.key1 = h1.key1
and h2.key2 = h1.key2
...
and h2.key9 = h1.key9)

 

[vivekm]

I think I know what you are asking! What you are asking is

1. How to get the missing records into the updated database? This question has been answered by Dagon.

2. How to effectively and efficiently update the data? In this case I hope you have got your update scripts sorted out - by the sounds of it - someone needs to sort this out. Anyways, your script should do the following:
Disable the constraints on the update side of the database.
Run the update script.
Enable the constraints.
Ensure data is same by running comparison queries and row counts.

Hope that helps.
Vivek