How to insert .xls data into SQL Table Query of Queries?

[johnbruso23]

Hi All,

I'm uploading my .xls using cffile and cfspreadsheet (query="data") to parse the .xls into a query object but I cannot figure out how to insert the query result into a table on sqlserver.

<!--- Take data from xls and put into Query, drop info into Table on SQLSERVER.ENGOPSMTRLS --->
<cfquery name="GetData" dbtype="query" datasource="ENGOPSMTRLS">
   SELECT * FROM data
</cfquery>

<cfoutput query="data"><P><BR>#Name#,<BR>#Work_Order#, <BR>#Project_Manager#, <BR>#Funding_Source#</cfoutput>

<cfdump var="#upload#" />
<cfdump var="#data#" />

<cfquery name="update" datasource="ENGOPSMTRLS">
INSERT INTO ENGOPSMTRLS_UPLOAD (Name, Work_Order)
<cfoutput query="data">
  VALUES (<cfqueryparam value="#data.Name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#data.Work_Order#" cfsqltype="cf_sql_varchar" />)
</cfoutput>
</cfquery>

Error Details:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'VALUES'.
The error occurred on line 228.

Thanks for your help.

[EddieLotter]

Firstly, it's probably a typo in your post but your query name is "GetData" not "data", right?

Secondly, try the following:

<cfquery name="update" datasource="ENGOPSMTRLS">
<cfloop query="GetData">
   INSERT INTO ENGOPSMTRLS_UPLOAD (Name, Work_Order)
   VALUES (<cfqueryparam value="#Name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#Work_Order#" cfsqltype="cf_sql_varchar" />)
</cfloop>
</cfquery>

Cheers

Eddie

[sdsinc_pmascari]

Take VALUES out of your loop and you need a comma in the SQL after each set of records:

<cfquery name="update" datasource="ENGOPSMTRLS">

INSERT INTO ENGOPSMTRLS_UPLOAD (Name, Work_Order)

VALUES

<cfoutput query="data">

  (<cfqueryparam value="#data.Name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#data.Work_Order#" cfsqltype="cf_sql_varchar" />),

</cfoutput>

</cfquery>