Why doesn't this SELECT statment have a FROM?
coldfusion, sql, t-sql
Solution
It looks like it is just `SELECT`ing scalar values, not records from any table. So
INSERT INTO myTable
SELECT 'foo'
UNION ALL
SELECT 'bar'
will insert two records into myTable, `foo` and `bar`.
Problem
``` <CFIF ListLen(SESSION.WHSurveyStruct.reasonString, ";") gt 0> <CFQUERY name="insertReasons" datasource="#REQUEST.dsn#"> INSERT INTO TWelcomeHome_Reason (ReasonID, SubReasonID, SurveyID) SELECT #sanitize(ListFirst(SESSION.WHSurveyStruct.reasonString, ";"))#, #sanitize(getLatestSurveyID.SurveyID)# <CFLOOP list="#sanitize(ListRest(SESSION.WHSurveyStruct.reasonString, ';'))#" index="thisReason" delimiters=";"> UNION ALL SELECT #sanitize(thisReason)#, #sanitize(getLatestSurveyID.SurveyID)# </CFLOOP> </CFQUERY> ``` I'm trying to understand what this does. I'm confused with the loop, why don't the select statements have a `FROM`? Ok they are just scalars. What about how there's one select statement on the outside of the loop and one on the inside? I sort of don't get the point on `union all`. And how come there are 3 columns being specified (ReasonID, SubReasonID, SurveyID) but in each `select` 2 values are given? dumped: struct CACHED: false EXECUTIONTIME: 0 RECORDCOUNT: 8 SQL: INSERT INTO TWelcomeHome_Reason (ReasonID, SubReasonID, SurveyID) SELECT 6, 18, 245 UNION ALL SELECT 6, 21, 245 UNION ALL SELECT 6, 24, 245 UNION ALL SELECT 3, 5, 245 UNION ALL SELECT 3, 6, 245 UNION ALL SELECT 3, 8, 245 UNION ALL SELECT 3, 11, 245 UNION ALL SELECT 3, 7, 245