Looping Through a SELECT Query Using a Cursor and Inserting Data into Another Table

Table of Contents

1. Introduction
2. Step 1: Understanding the Problem
3. Step 2: Setting Up the Tables
4. Step 3: Using a Cursor to Loop Through Data
5. Step 4: Explanation of the Code
6. Step 5: Verifying the Data
7. Conclusion

Introduction

In SQL Server, there are scenarios where you may need to loop through the results of a SELECT query and perform actions on each row, such as inserting data into another table. SQL Server provides a powerful mechanism for this using cursors.

In this tutorial, we will walk through the following steps:

• Looping through the results of a SELECT query using a cursor
• Inserting data from the selected rows into another table

Step 1: Understanding the Problem

Sometimes, you need to process each row from a SELECT statement individually. For example:

• Copying data from one table to another
• Performing row-by-row calculations or transformations

SQL Server’s CURSOR is an ideal solution for this, as it allows you to retrieve rows from a result set one by one and process them.

Step 2: Setting Up the Tables

Let’s start by creating two tables: a source table (Employees) and a destination table (EmployeeBackup).

Creating the Source Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
);
        

Creating the Destination Table

CREATE TABLE EmployeeBackup (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
);
        

Inserting Sample Data into the Source Table

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2020-01-01'),
       (2, 'Jane', 'Doe', '2021-02-15'),
       (3, 'Bob', 'Smith', '2022-03-30');
        

Step 3: Using a Cursor to Loop Through Data

Now, let’s use a cursor to loop through the data in the Employees table and insert it into the EmployeeBackup table.

DECLARE @EmployeeID INT;
DECLARE @FirstName NVARCHAR(50);
DECLARE @LastName NVARCHAR(50);
DECLARE @HireDate DATE;

-- Declare the cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees;

-- Open the cursor
OPEN EmployeeCursor;

-- Fetch the first row
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @HireDate;

-- Loop through all rows
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Insert into the backup table
    INSERT INTO EmployeeBackup (EmployeeID, FirstName, LastName, HireDate)
    VALUES (@EmployeeID, @FirstName, @LastName, @HireDate);

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @HireDate;
END;

-- Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
        

Step 4: Explanation of the Code

Let’s break down what’s happening in the code:

• Declaring Variables: Variables (@EmployeeID, @FirstName, etc.) store the data fetched from the cursor.
• Declaring the Cursor: The cursor selects all rows from the Employees table.
• Opening the Cursor: Opens the cursor and fetches the first row.
• Looping Through Rows: For each row, data is inserted into the EmployeeBackup table.
• Closing the Cursor: The cursor is closed and deallocated once all rows are processed.

Step 5: Verifying the Data

After running the cursor, you can verify the data inserted into the EmployeeBackup table using this query:

SELECT * FROM EmployeeBackup;
        

You should see the same data as in the Employees table.

Conclusion

In this tutorial, we’ve learned how to loop through the results of a SELECT query using a cursor in SQL Server. While cursors are powerful for row-by-row operations, keep in mind that they can be slower than set-based approaches for larger datasets. Use them when necessary and prefer set-based solutions when possible.