SQL Server: Use INSERT INTO SELECT Instead of a Cursor

INSERT INTO SELECT can copy a set of rows without fetching and inserting them one at a time. I learned that lesson during a code review early in my career. The working code was only the beginning of the conversation.

The Code Review I Still Remember

I was working as a network engineer in the United States. Our server inventory lived in a SQL Server table, and I needed to copy its rows into another table. I wrote a loop that inserted one row at a time.

Later, my team leader showed me a much shorter INSERT INTO SELECT statement. I shared the lesson at a team meeting and thanked my team leader for the guidance. That review helped me become more comfortable learning from my mistakes.

One correction to my original example: the code I published used a counting loop, not a declared cursor. A cursor is another way to process rows individually. Both are useful to compare with a set-based insert.

Build a Small Example

Run these blocks in order in the same query window. They use temporary tables, so no permanent tables are created. The source has four servers, and only three are active.

SET NOCOUNT ON;
CREATE TABLE #SourceServers
(
    ServerID int NOT NULL PRIMARY KEY,
    ServerName varchar(100) NOT NULL,
    IsActive bit NOT NULL
);
INSERT #SourceServers (ServerID, ServerName, IsActive)
VALUES (1, 'Alpha', 1), (3, 'Bravo', 1),
       (10, 'Charlie', 1), (20, 'Delta', 0);
CREATE TABLE #CursorCopy (ServerID int PRIMARY KEY, ServerName varchar(100));
CREATE TABLE #SetCopy (ServerID int PRIMARY KEY, ServerName varchar(100));
CREATE TABLE #LoopCopy (ServerID int PRIMARY KEY, ServerName varchar(100));

Copy Rows With a Cursor

This cursor fetches each active server and runs an INSERT for that row. It is intentionally simple: the only work inside the loop is the insert.

DECLARE @ServerID int, @ServerName varchar(100);
DECLARE ServerCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT ServerID, ServerName FROM #SourceServers WHERE IsActive = 1;
OPEN ServerCursor;
FETCH NEXT FROM ServerCursor INTO @ServerID, @ServerName;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT #CursorCopy (ServerID, ServerName)
    VALUES (@ServerID, @ServerName);
    FETCH NEXT FROM ServerCursor INTO @ServerID, @ServerName;
END;
CLOSE ServerCursor;
DEALLOCATE ServerCursor;

Copy the Same Rows as a Set

Here is the same copy in one statement. The column lists make the source and destination mapping explicit.

INSERT #SetCopy (ServerID, ServerName)
SELECT ServerID, ServerName
FROM #SourceServers
WHERE IsActive = 1;

Check both destinations rather than assuming they match. Each destination has a primary key, so the two EXCEPT checks are sufficient for this example.

SELECT 'Cursor' AS Method, ServerID, ServerName FROM #CursorCopy
UNION ALL
SELECT 'Set' AS Method, ServerID, ServerName FROM #SetCopy
ORDER BY Method, ServerID;
SELECT (SELECT COUNT(*) FROM #CursorCopy) AS CursorRows,
       (SELECT COUNT(*) FROM #SetCopy) AS SetRows,
       (SELECT COUNT(*) FROM
           (SELECT ServerID, ServerName FROM #CursorCopy
            EXCEPT SELECT ServerID, ServerName FROM #SetCopy) AS a) AS CursorOnly,
       (SELECT COUNT(*) FROM
           (SELECT ServerID, ServerName FROM #SetCopy
            EXCEPT SELECT ServerID, ServerName FROM #CursorCopy) AS b) AS SetOnly;

On my test, each method copied server IDs 1, 3 and 10. Both counts were 3, and CursorOnly and SetOnly were 0. The ORDER BY makes the displayed rows easy to compare; it does not promise insertion order.

The Hidden Problem in My Original Loop

My old example counted rows and then treated that count as an ID. That only works when IDs happen to match every number in the range. Real IDs can have gaps, as this small test shows.

DECLARE @Remaining int = (SELECT COUNT(*) FROM #SourceServers WHERE IsActive = 1);
WHILE @Remaining > 0
BEGIN
    INSERT #LoopCopy (ServerID, ServerName)
    SELECT ServerID, ServerName FROM #SourceServers
    WHERE IsActive = 1 AND ServerID = @Remaining;
    SET @Remaining -= 1;
END;
SELECT ServerID, ServerName FROM #LoopCopy ORDER BY ServerID;

There are three active rows, so the loop checks IDs 3, 2 and 1. It copies IDs 1 and 3 but misses ID 10. INSERT INTO SELECT uses the qualifying rows themselves and does not depend on consecutive IDs.

Before Replacing a Cursor

Check what the loop does besides inserting. A cursor may call a procedure, depend on order, or handle an error separately for each row. A single INSERT has a different statement boundary, which can affect triggers and error handling.

Review constraints, defaults, duplicate handling, and transaction requirements before changing production code. For large copies, test the plan, transaction log use, locking, and batch size on representative data. Set-based code is a strong starting point, not a promise that every workload behaves the same way.

If you add a NOT EXISTS filter for retries, consider concurrent sessions too. A unique constraint and an appropriate retry design help protect the rule. A filter alone does not guarantee that two sessions cannot insert the same key.

Clean Up

When you are finished, remove the temporary tables from this session.

DROP TABLE #LoopCopy, #SetCopy, #CursorCopy, #SourceServers;

The code review stayed with me because it was kind and practical. When the work is the same for every row, I now look for a way to express it as a set.

A shorter insert is useful, but a correct and understood insert is better.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.