I want to make a single SQL update call that updates multiple records at the same time. This can normally be done with an update with a select or an inner join:

UPDATE Persons
SET  Persons.PersonCityName=(SELECT AddressList.PostCode
                            FROM AddressList
                            WHERE AddressList.PersonId = Persons.PersonId)

or

UPDATE Per
SET 
Per.PersonCityName=Addr.City, 
Per.PersonPostCode=Addr.PostCode
FROM Persons Per
INNER JOIN
AddressList Addr
ON Per.PersonId = Addr.PersonId

However (using the example above) the Addresslist table is actually a CF dynamically generated query that does not exist in the SQL server DB. I could accomplish this by looping over the Addresslist CF query and updating one row at a time, but that would make a bunch of SQL server calls instead of a single SQL server call, albeit a much larger call.

you cold insert your data coming in into a sql table variable and then join on that with your update as listed above... you would still need to loop over your data from ColdFusion and could end up with a huge query... but it is one way. and could all be done in one transaction

Or insert all records into a temp table then join and update. https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert-multiple-rows/

Depending on your DB, you could potentially send the CF data as JSON and query against it as your table. Same goes for XML.

@dswitzer We use JSON in a bunch of areas.

JSON in sql server I didn't realize that was an option... wow talk about new thoughts on data import / sync tasks...

@Michael Schmidt It has to be SQL Server 2016 (13.x) compatibility mode 130 or higher

I would still probably let SQL parse it but store it still in columns to let a RDBMS do what it's good at set data...

Doing an insert as mentioned above has a limitation of 1000 records. Another way you can also accomplish below without creating a table using values constructor:

UPDATE v
	SET v.BuildTemplateID = BT.BuildTemplate
	FROM vlabResources_TMP v
	CROSS APPLY (
			VALUES
			<cfloop query="_getTemplates.data" >
			('#BuildTemplate#')<cfif currentrow neq RecordCount>,</cfif>
			</cfloop>
			) BT (BuildTemplate)
	WHERE v.BuildTemplateID is NULL
	AND v.vlabID = BT.LaunchTemplate