We may have to process SQL data row by row. In a traditional method, if we try to implement in SQL Sever T-SQL then, we have to use CURSOR.

It is a powerful command which helps SQL Developer to take control over dataset. We can define a cursor and manipulate data row by row with any direction (FIRST, LAST, PRIOR, NEXT, etc).

It has the ability to store a copy of the dataset into tempdb for the data processing by isolating the source table.

We can also define scope of the cursor LOCAL or GLOBAL for accessing it in local stored procedure or multiple stored procedures.

Cursor Definition

Step 1: DECLARE {cursor_name}  CURSOR FOR {select column1, column2, etc from table or view}

Step 2: OPEN <cursor_name>

Step 3: FETCH NEXT FROM <cursor_name> INTO <@variable1=column1>, <@variable2=column2>, etc


Step 5: code block

Step 6: FETCH NEXT FROM <cursor_name> INTO <@variable1=column1>, <@variable2=column2>, etc

Step 7: END [while]

Step 8: CLOSE <cursor_name>

Step 9: DEALLOCATE {cursor_name}

System Stored procedures for you

sp_cursor_list – Returns a list of cursors currently visible on the connection and their attributes.

sp_describe_cursor – Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.

sp_describe_cursor_columns – Describes the attributes of the columns in the cursor result set.

sp_describe_cursor_tables – Describes the base tables accessed by the cursor.

Cursor may be helpful in many ways for the SQL developers and have some performance impact while deal with it.

You can also consider using SSIS instead cursor while you are handling huge dataset.

SSIS definition to read dataset row by row

This SSIS package reads data from a table and add or replace an existing column with a user defined expression and store it in another table.

It also has many data transformation task for data processing on the fly. It handles dataset in memory very efficiently. There are many features available in Microsoft SQL Server Integration Services (SSIS). So, visit SQLServerRider.wordpress.com site and learn more about SSIS old and future version (SQL Server 2012).

Thanks for reading

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: