[ABANDONED] Chicken and Egg Problem

[Shaggy Hiker]

I have records in a table in one database, and have to move and transform any new ones into a table in a second database. On the face of it, that's not terrible. Most of the transforms are simple and easy enough, but a pair of them are sufficiently ugly that I'm not sure that SQL is the way to go here.

The record in question is a survey, which has a start point and an end point. What those points are can be pretty complicated, but I feel that I can simplify it down to saying that they are each a waypoint. Waypoints have to go into a different table.

Therefore, the steps end up being this:

1) Find a survey in table A that is not in table B.
2) For each such survey, take the start point and use it to create a record in table W which has a GUID as the PK.
3) The GUID for the new record in W becomes a field in the record in table B.
4) Repeat steps 2 and 3 for the end point.

Doing this in code is straightforwards, as it is a loop. You find a record, you generate a new waypoint record for the start and get the GUID from that record, then generate a new waypoint record for the end and get the GUID from that record. After that, you have all the information, so you insert the new record into table B.

Can it be done in SQL?