Skip to main content
kurumellasrujan
Participant
Jun 27, 2011 5:32 PM UTC
Question

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

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 is closed to new replies. Start a new post to keep the conversation going.

    7 replies

    Ken_Brewer
    Participant
    Aug 27, 2021 4:54 PM UTC

    <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>

    Translate
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    Sep 4, 2021 11:41 AM UTC

    @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>
    Translate
    Dan_Bracuk
    Inspiring
    Jun 27, 2011 7:36 PM UTC

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

    Translate
    kurumellasrujanAuthor
    Participant
    Jun 27, 2011 8:08 PM UTC

    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.

    Translate
    Adam Cameron.
    Inspiring
    Jun 27, 2011 8:32 PM UTC

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

    --

    Adam

    Translate
    Adam Cameron.
    Inspiring
    Jun 27, 2011 5:40 PM UTC

       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

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