<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
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With