Friday, November 9, 2007

Combining Select and Insert into the same CFQuery tag

So you want to grab some values and insert them into a table.
Here is the hard way.

<cfquery name="selectQuery" datasource="#application.dsn.db2#">
    SELECT Setting1,Setting2,Setting3
    FROM defaultSettings
    WHERE ID = 101
</cfquery>

<cfquery name="insertQuery" datasource="#application.dsn.db#">
INSERT INTO Settings
    (TS,
    Name,
    Setting1,
    Setting2,
    Setting3)
VALUES(GetDate(),
    'Test',
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting1#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting2#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting3#">)
</cfquery>


Here is the easier way, combining both Select and Insert in the same CFQuery block.

<cfquery name="insertQuery" datasource="#application.dsn.db#">
INSERT INTO Settings
    (TS,
    Name,
    Setting1,
    Setting2,
    Setting3)
SELECT GetDate(),
    'Test',
    DS.Setting1,
    DS.Setting2,
    DS.Setting3
FROM defaultSettings DS
WHERE DS.ID = 101
</cfquery>

No comments:

Post a Comment