At 01:09 PM 2/03/2005 -0600, Clay wrote:

>In porting over some Access tables, I ended up with more data than I need in
>some of the legacy tables after creating more tables.
>
>
>
>I have created a lookup table (TO_FROM_LOCATIONS) that holds and ID and
>Description for Locations. Another table (DEPARTURES) has both the "foreign
>key" ID value pointing to this Location table, but also contains the
>Description. TO_FROM_LOCATIONS is already partially populated, but does not
>have all the Location description data from the DEPARTURES table. I want to:
>
>
>
>1) Insert the Location description data from the legacy table into this
>lookup table
>
>2) Populate the ID column of the legacy table (a column I added) with
>the corresponding ID values from the Locations lookup table (I have a
>Generator for the ID and a BeforeInsert Trigger for the lookup table). I can
>do this like so:
>
>UPDATE DEPARTURES D
>
>SET DEPARTTO_ID =
>
>(SELECT ID FROM TO_FROM_LOCATIONS T
>
>WHERE T.LOCATION_DESC = D.DEPARTURE_TO)
>
>
>
>3) Drop the DEPARTURE_TO column from the legacy table, leaving the ID
>column/values
>
>
>
>What kind of SQL statement do I need to accomplish step 1 above (to populate
>the unique DEPARTURES.DEPARTURE_TO values into TO_FROM_LOCATIONS)? The
>closest I've come is the following, which doesn't work:
>
>
>
>INSERT INTO TO_FROM_LOCATIONS
>
>(LOCATION_DESC)
>
>VALUES
>
>(SELECT DISTINCT(DEPARTURE_TO) FROM DEPARTURES WHERE NOT EXISTS
>
>(SELECT ID FROM TO_FROM_LOCATIONS WHERE
>
>LOCATION_DESC = DEPARTURES.DEPARTURE_TO))

Write a stored procedure to do this. Start with an empty LOCATIONS table
and all nulls in DEPARTURES.DEPARTTO_ID. If you have already created the
foreign key constraint, drop it.

Your Before Insert trigger must look like this:

create trigger bi_locations for locations
active before insert as
begin
if (new.ID is not null) then
new.ID = gen_id(YourGenerator, 1);
end

Now, the SP:

create procedure merge_locations
as
declare description varchar(whatever) = '';
declare last_description varchar(whatever) = '';
declare id integer;
begin
for select DEPARTURE_TO
from DEPARTURES
for update
order by DEPARTURE_TO
into :description
as cursor CR
do
begin
if (description <> old_description) then
begin
id = gen_id(YourGenerator, 1);
insert into LOCATIONS (ID, LOCATION_DESC)
values (:id, :description);
end
update DEPARTURE
set DEPARTTO_ID = :id
where current of CR;
old_description = :description;
end
end

./hb