Multiple insert statement without counter

I often have a need to write an INSERT statement that adds multiple rows to a database. Obviously you can run any number of inserts separately, but that’s innefficient, so I loop over a list with a counter to know when to end. Recently I learned that the counter is unnecessary and you can create a fake SELECT statement to avoid needing a counter

<cfquery>
INSERT INTO Attendees (Schedule_ID, Attendee_ID, Meeting_ID, User_ID)
<cfloop list="#variables[eventlocation]#" index="i">
  SELECT #Schedule_ID#, #i#, #Meeting_ID#, 1
  UNION ALL
</cfloop>
<!--- returns no rows --->
SELECT 0, 0, 0, 0
WHERE 1 = 0
</cfquery>

For the sake of brevity I’ve avoided omitted the cfqueryparam’s that should always be used in queries.

Matt Busche's Picture

About Matt Busche

Software Engineer and Wheel of Fortune Expert If this article helped you, please consider buying me a book.

Des Moines, IA https://www.mrbusche.com