PL/SQL Newbie Help / Stored Procedure

I am trying to create several stored procudures that need to accomplish the same task. There is an import table, a map table, and a desintation table. I need to loop through the import table, match the item number to the mapped id and insert in a new destination table. There needs to be a contraint where if the id/date combination already exists the row in the destination table is updated vs. inserted.

This should be straightforward, but I keep trying and it does not seem to work for me. Originally I created a temporary view and used to map. Then I did a giant insert. And in some cases the number of columns in the import tables are large. It would be nice to have some checking and the capability to move to the next row if one row fails.

> select * import_table

item_no item_date item_desc
------- --------- ---------

1       12/01/2000  38572
2       01/01/2001  34759
3       02/01/2001  84576

> select * from map

id item_no
-- -------
99 1
98 2
83 3

I would like to create a desination table that looks like the following:

id item_date item_desc
--- --------- ---------

99  12/01/2000  38572
98  01/01/2001  34759
83  02/01/2001  84576

Thanks in advance for all your help.
Kim Received on Fri May 10 2002 - 17:39:46 CDT