Re: PL/SQL Newbie Help / Stored Procedure

From: Galen Boyer <galendboyer_at_yahoo.com>
Date: 10 May 2002 19:46:11 -0500
Message-ID: <u7kmbqr5o.fsf@rcn.com>

On 10 May 2002, kshorty_at_phat.com wrote:
> 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

Do an update of what exists and then an insert of new stuff.

update
(

   select item_desc_dest, item_desc_src
   from import_table src, map, dest

   where src.item_no = map.item_no
   and   map.id = dest.id 
   and   src.item_date = dest.item_date

) t1
set item_desc_dest = item_desc_src
;

insert into dest (id,item_date,item_desc) select map.id, src.item_date, src.item_desc from import_table src, map, dest

where src.item_no = map.item_no
and   map.id = dest.id (+)
and   src.item_date = dest.item_date (+)

and dest.id IS NULL
;

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Fri May 10 2002 - 19:46:11 CDT