Warm tip: This article is reproduced from stackoverflow.com, please click
coldfusion sql-server

Correct syntax for INSERT INTO SELECT query

发布于 2020-04-22 11:09:26

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.

Questioner
Max Voisard
Viewed
54
分享
Max Voisard 2020-02-07 04:02

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>

Related issues

1
Why "LAST_VALUE(birthdate)" window function clause necessary compare to "FIRST_VALUE(birthdate)"?
2
Group by query with rollup
3
SQL Server. How to refresh the intellisense?
4
Error "Cannot insert explicit value for identity column in table" using C#
5
SQL query pivot, move values to top
6
Best way to implement an audit trail in SQL Server?
7
TLS 1.2 SQL Server and Delphi Client
8
Count amount of same value
9
SQL Insert does column order matter
10
SQL Server query problem. example is in excel sheet picture