Skip to main content
K
kurumellasrujan
Participant
June 27, 2011
Question

Can we use Query of Queries to insert into a DB table?

  • June 27, 2011
  • 3 replies
  • 2386 views

Hi,

   Can we use Query of Queries to insert into database. For example:

   Here email value is obtained from an array.

   <cfquery name="chkexistingrecord" datasource="#DSN#">

   select * from records where email = #arrDat[3]#

   </cfquery>

   <cfif chkexistingrecord.recordcount gte 1>

   <cfquery name="insertduplicate" dbtype="query">

INSERT INTO [Duplicate_Records]

(

        [record created]

       ,[record updated]

       ,[email]

   values

        #chkexistingrecord['record created'][1]#

       ,#chkexistingrecord['record updated'][1]#

       ,#chkexistingrecord['email'][1]#

)

   </cfquery>

Both records and the Duplicate records tables are in the same database. When I

tried doing this I got the following error.

Message: Error Executing Database Query.

Detail: Query Of Queries syntax error.

Encountered "INSERT.

    This topic has been closed for replies.

    3 replies

    K
    Ken_Brewer
    Participant
    August 27, 2021

    <cfquery name="insertduplicate" datasource="#DSN#"> --- You need a datasource
    INSERT INTO [Duplicate_Records]([record created],[record updated] ,[email])
    values(
    <cfoutput query="chkexistingrecord">
    '#[record created]#','#[record updated]#' ,'#[email]#'), --- Add in single or multiple rows
    </cfquery>
    (null,null,null) --- Because you cannot not have a comma on the last entry
    </cfquery>

    <cfquery name="DeleteNULLS" datasource="#DSN#" >
    DELETE [Duplicate_Records] WHERE [record created] IS NULL --- Get rid of the last entry
    </cfquery>

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    September 4, 2021

    @Ken_Brewer , the code you gave is incorrect. Namely, the following bit misses a cfoutput closing tag:

    </cfquery>
(null,null,null) --- Because you cannot not have a comma on the last entry
</cfquery>
    D
    Dan_Bracuk
    Inspiring
    June 27, 2011

    In addition to Adam's answer, is there any particular reason you need a set of duplicate records in the first place? 

    K
    kurumellasrujanAuthor
    Participant
    June 27, 2011

    Yes i need the duplicate records for versioning i.e. each time a user updates a record the previous version will be available in the duplicate_records table.

    A
    Adam Cameron.
    Inspiring
    June 27, 2011

    Why don't you just do that with a DB trigger?  It's not the job of CF to handle DB-record versioning.

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    June 27, 2011

       Can we use Query of Queries to insert into database. For example:

    Well... no.  Think about what you're asking.  A QoQ performs SQL operations on a CF recordset object.  It does not involve a database.  So - by its very nature - how can it interact with a database?  What would it be INSERTing into?

    What you're going to need to do is to loop over the first record set, using that as a basis for the data for the INSERT queries.

    However can't you leave CF out of this, and just do it all on the DB?

    If you are needing to use CF for some reason, remember to always parameterise your dynamic values, too.  Don't hard-code them into your SQL strings.

    --

    Adam

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