SQL Server Loop Example

I needed to run a LOOP statement in SQL. Normally I would do this with website server-side code (ex. PHP or Node.js), however this was needed for data analysis that I otherwise didn’t have a need for writing web-based code.

I needed a solution compatible with MS SQL Server Management Studio ("SSMS").

Why even do this?

I had a database table with millions of rows and I needed to create a table with a subset of this data.

To create the table, I needed to run several INSERT queries; each having only slightly different WHERE criteria.

By using a LOOP, I could dynamically create the table- and have a repeatable process as it would have to be done often.

How to write a loop using SQL

Here’s the code, explanation is below.

	DROP TABLE IF EXISTS #temp
	CREATE TABLE #temp (
	id INT IDENTITY (1,1)
	, org VARCHAR(50)
	)
	INSERT INTO #temp (org)
	SELECT 'aaa' UNION
	SELECT 'bbbb' UNION
	SELECT 'cccc' UNION
	SELECT 'dddd' UNION
	SELECT 'eeee'
	--SELECT * FROM #TEMP
	DECLARE @theOrg VARCHAR(50) = '';
	DECLARE @cursorId INT = 1;
	DECLARE @rowCount INT = ( SELECT COUNT(*) FROM #temp);
	WHILE @cursorId <= @rowCount
	BEGIN
	SET @theOrg = (SELECT org FROM #temp WHERE id = @cursorId);
	SELECT @theOrg
	SET @cursorId = @cursorId + 1
	END

view raw sql-loop-example.sql hosted with ❤ by GitHub

Let’s start with some fake data.

• Rows 1–5 create a temporary table
• Rows 7–13 populate this table with data
• The value passed into my WHERE criteria (in my eventual INSERT statement) would be dynamically changing as it loops. The value will be stored in a variable called theOrg, declared on line 17.
• I’ll need to loop through a finite number of rows: starting at “1” (Row 18), and up through the number of rows in my temporary table (Row 19)
• Rows 21–29 is the actual loop:
• Row 24 gets the specific value that I’ll ultimately be querying on, based on a SELECT query using the variable cursorId
• Row 25 is where I could run an INSERT statement to generate the table, although here I’m using a SELECT statement for demonstration purposes
• Row 27 increments the variable cursorId so that the loop will continue
• Running it in SSMS, you’ll see this:

Of course, now I can swap out the SELECT statement (Row 25) and use my real query (with an INSERT statement) to generate my dataset.