Query cf queryObject and insert into table

Question

I'm passing queryObject into a CFC. I can writeDump(myQryObject) and I see the queryObjects contents and all is good up to this point. I can write a select statement and dump a row(s) depending on my query - again, all good here. I need to now insert the data into a table but I'm not getting the syntax right.

The CFC is written in CFScript.

local.blkLoadQry = new Query(); // new query object     
local.blkLoadQry.setDBType("query");
local.blkLoadQry.setAttributes(sourceQuery=arguments.blkdata);
local.blkLoadQry.addParam(name="batchid",value=arguments.batchID,cfsqltype="cf_sql_varchar",maxlength="36");

local.blkLoadQry.setSQL("
INSERT INTO bulkloadtemptable (
      uuid
      , gradyear
      , firstName
      , lastName
      , email
  )
  SELECT 
      :batchid
      , `Graduation Year`
      , `Jersey`
      , `First Name`
      , `Last Name`
      , `Email`
  FROM 
      bulkloadtemptable_copy
  WHERE uuid = :batchid
");

`Lexical error at line 10, column 17. Encountered: "`" (96), after : ""`

This is the error I'm getting but the line numbers of the errors don't line up with my expectations so that's what brings me here. :batchid would be line 10.

What am I missing?

Answer 1

You are attempting something impossible. Your query of queries select statement runs in ColdFusion only. There is no database connection in play.

If you want to insert data from a ColdFusion query into a database, you have to loop through the rows somehow. You can have an insert query inside a loop or a loop inside an insert query. Here is sample syntax for both.

Query inside loop.

<cfoutput query="cfQueryObject">
<cfquery datasource = "aRealDatabase">
insert into table
(field1
, field2
, etc)
values
(<cfqueryparam value = "#cfQueryObject.field1#">
, <cfqueryparam value = "#cfQueryObject.field1#">
, etc
)
</cfquery>
</cfoutput>

Loop inside query

<cfquery datasource = "aRealDatabase">
insert into table
(field1
, field2
, etc)

select null
, null
, etc
from someSmallTable
where 1 = 2
<cfoutput query="cfQueryObject">
union
select <cfqueryparam value = "#cfQueryObject.field1#">
, <cfqueryparam value = "#cfQueryObject.field1#">
, etc
from someSmallTable
</cfoutput>
</cfquery>

You can experiment to see what works better in your situation.