Highlighted

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

Explorer ,
Jan 14, 2016

Copy link to clipboard

Copied

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.

Views

362

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Explorer ,
Jan 14, 2016

Copy link to clipboard

Copied

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.

Views

363

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 14, 2016 0
Advocate ,
Jan 14, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 14, 2016 0
Engaged ,
Jan 15, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 15, 2016 0