What are some alternatives to using a cursor in SQL Server 2008?

Question

I've been creating imports that use SSIS to import data into a temp table, then using a stored procedure, steps through the data one by one with a cursor to process the data and insert information into 3 different tables. The inserts in the first 2 tables are complicated because if there is a record that already exists with the same data the record is not created. Whether the a record is inserted or not in the first 2 tables the ID of the record or matching record is returned to be used on the 3rd table. Is there an alternative to using the cursor?

Answer 1

Without seeing your current code it is difficult to know whether this would be suitable but I'd look at

1. the MERGE statement (allows actions to be specified for the different cases "when matched", "when not matched by target", "when not matched by source") and
2. the OUTPUT clause (allows you to capture the newly updated records for processing).

Answer 2

This sounds like the perfect candidate for replacing a cursor with a combination of table variables and a while loop (which, multiple people have tested and confirmed, is nearly always more performant than a cursor).

Answer 3

Hopefully some ideas from this will help. If you still need help avoiding a cursor, we need to see a better example of the processing you are doing in the cursor.

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them