In this article we will show how to iterate through a recordset in TSQL using cursors. In this example, we will select some records and then insert the ID into a related table.

Have you ever want to loop through a recordset and then perform another database operation as you go through the recordset? You may have considered doing this in the ASP.NET page, but it can really slow down your application making repeated calls over the network to connect to the database server from the Web server. In this code snip, we going to show you how you can implement cursors in your stored procedures in SQL.

Alter PROCEDURE dbo.sp_InsertRecords
AS
Declare @id int
-- first we'll pull some records out of our table.
DECLARE CursorQuery CURSOR FOR
            SELECT ID FROM myTable WHERE MyRecord > 15
OPEN CursorQuery
-- we're going to fetch our record into the ID variable which we'll use for inserting a related record.
FETCH NEXT FROM CursorQuery
INTO @ID
PRINT 'Record Status' +  CAST(@@FETCH_STATUS as varchar)
WHILE @@FETCH_STATUS = 0
BEGIN
-- now insert the id from the main table into the related table.
            INSERT INTO
                        RelatedTable
                        (relatedID)
            VALUES
                        (@id)                   
            FETCH NEXT FROM CursorQuery
            INTO @ID
END
CLOSE CursorQuery
DEALLOCATE CursorQuery
RETURN

Basically what we're doing here is executing a SQL statement that is going to return a recordset, and then declare a cursor so we can iterate through the records. As we iterate through the records we will insert the ID field value into the RelatedTable's relatedID field. Just remember that you need to FETCH the value into your variable in this case @ID.