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:
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.
Top comments (0)