Skip to main content
J
johnbruso23
Inspiring
January 14, 2016
Question

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

  • January 14, 2016
  • 2 replies
  • 563 views

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.

    This topic has been closed for replies.

    2 replies

    S
    sdsinc_pmascari
    Legend
    January 15, 2016

    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>

    EddieLotter
    EddieLotter
    Inspiring
    January 14, 2016

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices