titsiros (1/26/2016)
The databases are on different machines but for the purpose of the merge we could move them to the same one. This would also make sense in terms of performance.Unfortunately not using SSIS. I know i could use any programming language like C++ to iterate through the select statement results and generate an insert for each record in order to capture individual errors but worried this will impact performance...some tables have 80 million records.
I was hoping to use insert/select statements which is neater and faster
Iterating is iterating no matter the language. I would stay with Set based SQL. My suggestion here, you determine if feasible, is to take the smaller of the sets and copy that table entirely to the database with the bigger set. This is ideal, but in any case you want to copy the other machine's table over into a temp table on the machine you are working on. Then you can even put a clustered index on that new temp table on the join column (especially since you mention a non small amount of rows). Working this way you avoid the linked server (if that is what you are using) from doing the joins across the network in your insert/update/delete operations.
I would explain to someone how SSIS would help here with logging and rollbacks as well to try to make that a possibility at least going forward.
----------------------------------------------------