Insert/Update Table

[reddy]

Hello Sirs,

I have master customer file and 4 ODBC Dataware house tables like cust_address,country,region,geography tables.

I have to Insert all records into cust_address table where no customer key and customer addrtype = 's' not found in cust_address table when matched with master customer file using custno key field.

I have to update cust_address table when current record is differ from existing master record based on fields
geography key,address1,address2,postal code,phone number...

Can you suggest me how to create the job.

Thanks
Narasa

[kris]

Here is my aproach to this:

1. Create a table Master_Customer and load with your master file only key fields or necessary fields.

2. your job should look like:

Code: Select all

Join tables in (plugin stage)
Master_Customer ------------> Hash File
cust_address                |
and necessary tables   	  |
                             -----Insert---->
Master file   --------Transformer      cust_address(plug-in stage)
                             -----Update--->

3. Hash file should have all the necessary key fields that you should be checking to decide whether it is insert or update.
4. Give proper constraints for insert update links.

Kris~



[/code]

[SriKara]

Yes i agree with kris.
Loading the keys of the master customer file into a table in the staging area..
and then doing the join between staging table and target tables to create the hash file, to figure out I / U is an efficient way of loading the dimensions of a star schema.