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))



Clay Shannon,

Dimension 4 Software





[Non-text portions of this message have been removed]