I updated the question details and provided the sample data.

ChrisM@Work : took your suggestion and came up with a much better query plan. Execution was too fast within few minutes. I still got to validate the data 🙂 But...

In your block above can I output the [existing row ID] from the source table (HouseholdTemp) without inserting it in the Locations or Individuals table ?

Thanks for response. So if I understand it correct from the above ....

one block of insert for locations and another for individuals

INSERT into dbo.Locations ...SELECT ....FROM dbo.HouseholdTemp WHERE <conditions> --...

Thanks for the response ChrisM@Work. This query performs way better in SQL2019. Why its not possible to get similar or even close to that response in SQL2016.

I need to get...

• This reply was modified 4 years, 5 months ago by  marcus.
• This reply was modified 4 years, 5 months ago by  marcus.