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.

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:

Multiple queries are run, based on a loop

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.