Correct syntax for INSERT INTO SELECT query

Question

I am trying to insert records that are deleted from one table into another so I can archive the deleted records, sort of like the Windows Recycle Bin. To do that, I use an INSERT INTO SELECT query:

<cfquery name="archiveDeletion" datasource="#application.dsn#">
    INSERT INTO deletedCylinderContentRecords (air_emissions, barcode, building, capacity, Carcinogen, carcinogen_comment, carcinogen_type, cas, casORmixture, chemicalname, concentration, containermaterial, containertype, cyanide, CylinderID, dotdivision, dothazardclass, dothazardclassSub, expirationdate, flashpoint, formula, grade, HMAR_ID, hydroflouric_acid, HydrostaticTestDate, HydrostaticTestDueDate, initial_quantity, inventoried, isdeleted, lastupdated, LeakTestDate, location, manufacturer, ModifiedBy, msds, MSDS_Scan, MSN, Mutagen, nfpablue, nfpared, nfpawater, nfpayellow, ownerid, PeroxideConcentration, PeroxideFormers, PeroxideTestDate, PeroxideTestDueDate, phaseID, PList, price, Purchase_Scan, Purchase_Scan_Link, purchasedate, remarks, ResearchSampleNo, room, sewer, shelf, ShockSensitive, specificgravity, spillage, spillclass, SPInspect, SPInspectDate, SPInterval, stocknumber, StorageRemarks, Teratogen, Undelete, units, usage_comments, usage_transferred, usage_transferred_to, usedinprocess, Validate, ValidateBy, ValidateDate, vendor, VendorNumber, waste, deleterName, timeDeleted, reasonDeleted)
    SELECT * FROM containers WHERE barcode = '#session.barcode#',
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#getDeleterName.last_name#, #getDeleterName.first_name# #getDeleterName.middle_initial#.">,
    SYSDATETIME(),
   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Form.deleteReason5#">
</cfquery>

From the above query, you can see all but three columns are inserted from the original table into the archiving table. Those are the last three: deleterName, timeDeleted, and reasonDeleted. So after the SELECT * portion, I add the three columns I want, a couple with <cfqueryparam> tags. My syntax must be wrong, as I get this error:

Incorrect syntax near ','.

What is the correct syntax? Do I need another SELECT keyword? I can't find anything on this.

Answer 1

@Max Voisard, I've go through your code. I've find the some more issues in your entire query.

• In your select query you have WHERE condition like WHERE barcode = '#session.barcode#', ( We don't need comma here. ) after that you have wrote some cfqueryparam. Which is entirely wrong . You should use either AND / OR operator to check the next condition with some columns.
  

• In your first queryParam you have used list of values in value attribute then you should use "List = true". So your query should be like

    WHERE barcode = '#session.barcode#' 
              AND / OR 
    columnName = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#getDeleterName.last_name#, #getDeleterName.first_name# #getDeleterName.middle_initial#." list='true'>
  

• And as per above all suggestion don't use * in your SELECT query. Try to put mentioned column names. Here is my sample query for you with some conditions.

INSERT INTO table1(id,name,age) SELECT id,name,age FROM table2 WHERE age < 10 AND id < 9

I hope this will help you more :). Thanks.

Answer 2

Thanks to what @SeanLange said in the comments, I found the solution. I added the three extra columns not part of the containers table into the SELECT query, and sure enough it worked. I also explicitly named the columns instead of using the * selector, so the final solution looked like this:

<cfquery name="archiveDeletion" datasource="#application.dsn#">
    INSERT INTO deletedCylinderContentRecords (air_emissions, barcode, building, capacity, 
    Carcinogen, carcinogen_comment, carcinogen_type, cas, casORmixture, chemicalname, concentration,
    containermaterial, containertype, cyanide, CylinderID, dotdivision, dothazardclass,
    dothazardclassSub, expirationdate, flashpoint, formula, grade, HMAR_ID, hydroflouric_acid,
    HydrostaticTestDate, HydrostaticTestDueDate, initial_quantity, inventoried, isdeleted, lastupdated, 
    LeakTestDate, location, manufacturer, ModifiedBy, msds, MSDS_Scan, MSN, Mutagen, nfpablue, nfpared,
    nfpawater, nfpayellow, ownerid, PeroxideConcentration, PeroxideFormers, PeroxideTestDate,
    PeroxideTestDueDate, phaseID, PList, price, Purchase_Scan, Purchase_Scan_Link, purchasedate,
    remarks, ResearchSampleNo, room, sewer, shelf, ShockSensitive, specificgravity, spillage,
    spillclass, SPInspect, SPInspectDate, SPInterval, stocknumber, StorageRemarks, Teratogen,
    Undelete, units, usage_comments, usage_transferred, usage_transferred_to, usedinprocess, Validate, 
    ValidateBy, ValidateDate, vendor, VendorNumber, waste, deleterName, timeDeleted, reasonDeleted)
    SELECT air_emissions, barcode, building, capacity, Carcinogen, carcinogen_comment, carcinogen_type,
    cas, casORmixture, chemicalname, concentration, containermaterial, containertype, cyanide, 
    CylinderID, dotdivision, dothazardclass, dothazardclassSub, expirationdate, flashpoint, formula,
    grade, HMAR_ID, hydroflouric_acid, HydrostaticTestDate, HydrostaticTestDueDate, initial_quantity,
    inventoried, isdeleted, lastupdated, LeakTestDate, location, manufacturer, ModifiedBy, msds, 
    MSDS_Scan, MSN, Mutagen, nfpablue, nfpared, nfpawater, nfpayellow, ownerid, PeroxideConcentration,
    PeroxideFormers, PeroxideTestDate, PeroxideTestDueDate, phaseID, PList, price, Purchase_Scan,
    Purchase_Scan_Link, purchasedate, remarks, ResearchSampleNo, room, sewer, shelf, ShockSensitive,
    specificgravity, spillage, spillclass, SPInspect, SPInspectDate, SPInterval, stocknumber,
    StorageRemarks, Teratogen, Undelete, units, usage_comments, usage_transferred, usage_transferred_to, 
    usedinprocess, Validate, ValidateBy, ValidateDate, vendor, VendorNumber, waste,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#getDeleterName.last_name#, #getDeleterName.first_name# #getDeleterName.middle_initial#.">,
    SYSDATETIME(),
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Form.deleteReason5#">
    FROM containers WHERE barcode = '#session.barcode#'
</cfquery>

Answer 3

This is comment that is way too long to fit into comments

Let me make it readable

<cfquery name="archiveDeletion" datasource="#application.dsn#">
    INSERT INTO deletedCylinderContentRecords (
      air_emissions, barcode, building, capacity, Carcinogen, 
      carcinogen_comment, carcinogen_type, cas, casORmixture, 
      chemicalname, concentration, containermaterial, containertype, 
      cyanide, CylinderID, dotdivision, dothazardclass,
      dothazardclassSub, expirationdate, flashpoint, formula, grade,
      HMAR_ID, hydroflouric_acid, HydrostaticTestDate, 
      HydrostaticTestDueDate, initial_quantity, inventoried, 
      isdeleted, lastupdated, LeakTestDate, location, manufacturer,
      ModifiedBy, msds, MSDS_Scan, MSN, Mutagen, nfpablue, nfpared,
      nfpawater, nfpayellow, ownerid, PeroxideConcentration, 
      PeroxideFormers, PeroxideTestDate, PeroxideTestDueDate, phaseID,
      PList, price, Purchase_Scan, Purchase_Scan_Link, purchasedate, 
      remarks, ResearchSampleNo, room, sewer, shelf, ShockSensitive, 
      specificgravity, spillage, spillclass, SPInspect, SPInspectDate, 
      SPInterval, stocknumber, StorageRemarks, Teratogen, Undelete, 
      units, usage_comments, usage_transferred, usage_transferred_to, 
      usedinprocess, Validate, ValidateBy, ValidateDate, vendor, 
      VendorNumber, waste, deleterName, timeDeleted, reasonDeleted)
    SELECT * 
    FROM containers 
    WHERE barcode = '#session.barcode#',
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#getDeleterName.last_name#, #getDeleterName.first_name# #getDeleterName.middle_initial#.">,
    SYSDATETIME(),
   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Form.deleteReason5#">
</cfquery>

Back to the issue at hand.

I have no dea what this is supposed to be. Are you trying to do a bunch of ANDs? Are you trying to add more columns to the select? Why is barcode not in cfqueryparam Param which a bunch of pieces of data looks really strange. I would expect to see three params for the three parts of the name. Then again maybe that is how data is stored. Last but not least, I think all the columns have to be listed in the select statement. Not sure * will work (I could be wrong)

    SELECT * 
    FROM containers 
    WHERE barcode = '#session.barcode#',
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#getDeleterName.last_name#, #getDeleterName.first_name# #getDeleterName.middle_initial#.">,
    SYSDATETIME(),
   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Form.deleteReason5#">