Inserting rows into a table from another table without iterating over a cursor

Question

I have the below tables, Robot and RobotTestResult. I want to migrate the DateTested field in Robot to the DateTested field in RobotTestResult for the corresponding Robot.

Robot         RobotTestResult
--------      ---------------
RobotID       RobotTestID (Identity)
DateTested    RobotID
              DateTested

There will be at most 1 entry in the RobotTestResult table for any Robot

Some Robots will have a corresponding entry in the RobotTestResult table, I can update those values with a simple join:

UPDATE RTR
SET RTR.DateTested = r.DateTested
FROM [dbo].[RobotTestResult] RTR
JOIN [Robot] r
ON RTR.RobotID = r.RobotID;

The problem is with Robots who do not have an entry in the RobotTestResult table. The only way I can think of is to use a Cursor to iterate through each Robot that does not have an RTR entry and do an insert, but I feel like there must be a more efficient way.

Edit to add: If no DateTested value exists in Robot, no RobotTestResult should be inserted.

Answer 1

I prefer using NOT EXISTS for this scenario as it matches the logic of the problem.

INSERT INTO RobotTestResults (RobotID, DatedTest)
    SELECT RobotID, DateTest
    FROM Robot R
    WHERE DateTest IS NOT NULL
    AND NOT EXISTS (
        SELECT 1
        FROM RobotTestRules RTR
        WHERE RTR.RobotID = R.RobotID
    )