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

sql-server-2008 cursor data-import
norlando picture norlando · Aug 4, 2010 · Viewed 8.5k times · Source

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

Martin Smith picture Martin Smith · Aug 4, 2010

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).

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More