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

[kurumellasrujan]

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.

[Adam Cameron.]

   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

[Dan_Bracuk]

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

[kurumellasrujan]

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.

[Adam Cameron.]

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

--

Adam

[-__cfSearching__-]

As an aside, if "duplicate_records" represents some sort of history / audit table you may want to chose a different naming convention. Going strictly off of objects names, I thought the code was searching for duplicate records

[Ken_Brewer]

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

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