[ABANDONED] Chicken and Egg Problem

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?

Yes, it can be done.

The general idea is that you:

1. Get all records in table A that do not exist in table B
2. Do a bulk insert into table W from the results of step 1
3. Do a bulk update of table B using the GUID from table W
4. Repeat for the endpoints

Please excuse this example because I don't know your schema, but pseudo-code looks something like:

Code:

---

BEGIN TRANSACTION;
BEGIN TRY;

-- step 1 and 2
INSERT INTO TableW (guid, waypoint_id)
    SELECT
        NEWID()
        , waypoint_id
    FROM
        TableA
    WHERE NOT EXISTS (
        SELECT
            1
        FROM
            TableB
        WHERE
            TableA.survey_id = TableB.survey_id
    )
;

-- step 3
UPDATE TableB SET
    start_guid = W.guid
FROM
    TableB
    LEFT OUTER JOIN (
        SELECT
            TableA.survey_id
            , TableW.guid
        FROM
            TableA
            LEFT OUTER JOIN TableW ON TableA.waypoint_id = TableW.waypoint_id
        WHERE NOT EXISTS (
            SELECT
                1
            FROM
                TableB
            WHERE
                TableA.survey_id = TableB.survey_id
        )
    ) AS W ON TableB.survey_id = W.survey_id;

-- do the same for the end_guid

END TRY;
COMMIT TRANSACTION;

---

I hope I'm not missing anything.

Also keep in mind that there is also the maintainability issue. While doing this in raw SQL would certainly be faster, it would be a you know what to maintain. You have to balance speed and maintainability. If I were the architect making the decision here, I would probably just say to create a migration and do this in C#/VB.NET/Whatever so that any developer looking at it several years from now can infer why you are doing what you did.

Within an hour of posting this question, new information appeared which upended the entire approach. When I wrote the initial post, I thought that I was moving data from DB A to DB B on server S. What I realized, is that I'm moving data from some form of a DB with a design like A (though maybe not exactly A), to some other database that could be B or could be C, and the A would be in one of a couple different locations, while B would always be on server S, and C could be...well, pretty nearly anywhere.

That pretty much rules out a stored procedure. What I'll be doing is writing code that can be used in a service and used in the heart of some other desktop application, or two.

That's something I'm quite comfortable with, and therefore I am abandoning this question.