Skip to main content
A
ACS LLC
Inspiring
September 19, 2018
Answered

Moving multiple sets of data from one table to another without multiple queries in CFLOOP

  • September 19, 2018
  • 3 replies
  • 1580 views

(CF2016/MSSQL 2008)

I have a need to move data from one table to another for multiple records.

At the moment I am using a CFLOOP to execute multiple queries which I know is not an efficient way to do it.

The data types are the same, although the field names in each table is different.

I believe it can be done with an insert select statement, something along these lines:

<CFQUERY name="MoveData" Datasource="MyDatasource">

INSERT INTO DestinationTable (DestinationField_1,DestinationField_2,DestinationField_3)

SELECT (SourceField_1,SourceField_2,SourceField_3)

FROM SourceTable

WHERE Something = MatchingValue

</CFQUERY>

However, I am a little stuck, because one of the values, let's say for arguments sake it's SourceField_2 will not come from the SELECT query from the destination table, it is actually a value which is part of a submitted list that I am looping over.

<CFLOOP LIST="#submitted_game_uid#" INDEX="game_uid">

So what I want to do is open the CFQUERY above the loop, and then try to execute as a single CFQUERY that moves the data in the order of the list, and constructs a single move of the data which includes the correct game_uid values for each insert.

Can anybody help throw some light on this for me

Thanks.

This topic has been closed for replies.
Correct answer BKBK

I just came up for air after a couple of long days and thought I'd take a minute to revisit this ready for tomorrow.

It looks like I'm not actually sure about how to handle the query build within the loop.

I do indeed have several loops where I put the CFQUERY outside of it and then build the the query inside the loop so as discussed it's one payload rather than 50 individual queries

However after working on this particular piece I've found that I have to add in two pieces of data from a previous query. So The question now is how could I tweak this so that it goes in one shot rather than individual queries? Presume that form_game_uid could be a list of up to 50 unique values and the query must append these values are they came out of the GetGamesAff query.

<CFSET AffiliateList = #Valuelist(GetGamesAff.gamecredit_affiliate_uid)#>

<CFSET RedemptioncodeList = #Valuelist(GetGamesAff.gamecredit_redemptioncode_uid)#>

<CFSET ListPosition = "0">

<CFLOOP LIST="#form_game_uid#" INDEX="game_uid">

<CFSET ListPosition = ListPosition + 1>

<CFQUERY NAME="AddGame" DATASOURCE="#datasource#">

INSERT INTO endusergames

(

endusergames_enduser_uid,

endusergames_game_uid,

endusergames_affiliate_uid,

endusergames_redemptioncode_uid

)

VALUES

(

<cfqueryparam value="#session.useruid#" cfsqltype="CF_SQL_INT">,

<cfqueryparam value="#game_uid#" cfsqltype="CF_SQL_TINYINT">,

<cfqueryparam value="#val(ListGetAt(affiliatelist,ListPosition))#" cfsqltype="CF_SQL_INT">,

<cfqueryparam value="#val(ListGetAt(Redemptioncodelist,ListPosition))#" cfsqltype="CF_SQL_INT">

)

</CFQUERY>

</CFLOOP>


https://forums.adobe.com/people/ACS+LLC  wrote

Presume that form_game_uid could be a list of up to 50 unique values and the query must append these values are they came out of the GetGamesAff query.

Then you should place the loop not outside, but within the query, as follows:

<cfset affiliateList = valueList(getGamesAff.gamecredit_affiliate_uid)>

<cfset redemptionCodeList = valueList(getGamesAff.gamecredit_redemptioncode_uid)>

<cfset listPosition = "0">

<cfquery name="addgame" datasource="#datasource#">

    insert into endusergames

    (

        endusergames_enduser_uid,

        endusergames_game_uid,

        endusergames_affiliate_uid,

        endusergames_redemptioncode_uid

    )

    values

    <cfloop list="#form_game_uid#" index="game_uid">

    <cfset listPosition = listPosition + 1>

    (

        <cfqueryparam value="#session.useruid#" cfsqltype="cf_sql_int">,

        <cfqueryparam value="#game_uid#" cfsqltype="cf_sql_tinyint">,

        <cfqueryparam value="#val(listgetat(affiliatelist, listPosition))#" cfsqltype="cf_sql_int">,

        <cfqueryparam value="#val(listgetat(redemptioncodelist, listPosition))#" cfsqltype="cf_sql_int">

    )<cfif listPosition lt listLen(form_game_uid)>,</cfif>

    </cfloop>

</cfquery>

3 replies

D
Dave Watts
Community Expert
September 20, 2018

I don't think you'll be able to solve this with a single INSERT INTO SELECT statement, which is designed to select from one table and insert into another all at once. But that doesn't mean you can't solve this problem on the database side instead of the CF side. In brief, you could write a stored procedure that (a) accepts your list of values as a single variable, (b) parses it into a list, (c) iterates through that list, and (d) inserts each entry individually.

I'm too lazy to actually write that in SQL, but you get the idea.

Dave Watts, Fig Leaf Software

Dave Watts, Eidolon LLC
A
ACS LLCAuthor
Inspiring
September 20, 2018

Dave - I think if I try that I'll be swimming out of my depth, it won't go well.

I think I'm best trying to get Wolfshades initial suggest into play. Moving the data in one swoop would be ideal as I have to loop over as many as 50 records each time this needs to run. However as I am also assigning another value in the mix which goes with that data into the other table, and that value is in a particular order and each record has a unique value, it looks like I'm probably best with a loop.

If I can just avoid 50 CFQUERY's and have it put the full payload into one CFQUERY by building the query inside a loop within the CFQUERY then I think that's probably my best bet, anything beyond that would be too tricky for me to create

    BKBK
    BKBKCorrect answer
    Community Expert
    September 23, 2018

    I just came up for air after a couple of long days and thought I'd take a minute to revisit this ready for tomorrow.

    It looks like I'm not actually sure about how to handle the query build within the loop.

    I do indeed have several loops where I put the CFQUERY outside of it and then build the the query inside the loop so as discussed it's one payload rather than 50 individual queries

    However after working on this particular piece I've found that I have to add in two pieces of data from a previous query. So The question now is how could I tweak this so that it goes in one shot rather than individual queries? Presume that form_game_uid could be a list of up to 50 unique values and the query must append these values are they came out of the GetGamesAff query.

    <CFSET AffiliateList = #Valuelist(GetGamesAff.gamecredit_affiliate_uid)#>

    <CFSET RedemptioncodeList = #Valuelist(GetGamesAff.gamecredit_redemptioncode_uid)#>

    <CFSET ListPosition = "0">

    <CFLOOP LIST="#form_game_uid#" INDEX="game_uid">

    <CFSET ListPosition = ListPosition + 1>

    <CFQUERY NAME="AddGame" DATASOURCE="#datasource#">

    INSERT INTO endusergames

    (

    endusergames_enduser_uid,

    endusergames_game_uid,

    endusergames_affiliate_uid,

    endusergames_redemptioncode_uid

    )

    VALUES

    (

    <cfqueryparam value="#session.useruid#" cfsqltype="CF_SQL_INT">,

    <cfqueryparam value="#game_uid#" cfsqltype="CF_SQL_TINYINT">,

    <cfqueryparam value="#val(ListGetAt(affiliatelist,ListPosition))#" cfsqltype="CF_SQL_INT">,

    <cfqueryparam value="#val(ListGetAt(Redemptioncodelist,ListPosition))#" cfsqltype="CF_SQL_INT">

    )

    </CFQUERY>

    </CFLOOP>


    https://forums.adobe.com/people/ACS+LLC  wrote

    Presume that form_game_uid could be a list of up to 50 unique values and the query must append these values are they came out of the GetGamesAff query.

    Then you should place the loop not outside, but within the query, as follows:

    <cfset affiliateList = valueList(getGamesAff.gamecredit_affiliate_uid)>

    <cfset redemptionCodeList = valueList(getGamesAff.gamecredit_redemptioncode_uid)>

    <cfset listPosition = "0">

    <cfquery name="addgame" datasource="#datasource#">

        insert into endusergames

        (

            endusergames_enduser_uid,

            endusergames_game_uid,

            endusergames_affiliate_uid,

            endusergames_redemptioncode_uid

        )

        values

        <cfloop list="#form_game_uid#" index="game_uid">

        <cfset listPosition = listPosition + 1>

        (

            <cfqueryparam value="#session.useruid#" cfsqltype="cf_sql_int">,

            <cfqueryparam value="#game_uid#" cfsqltype="cf_sql_tinyint">,

            <cfqueryparam value="#val(listgetat(affiliatelist, listPosition))#" cfsqltype="cf_sql_int">,

            <cfqueryparam value="#val(listgetat(redemptioncodelist, listPosition))#" cfsqltype="cf_sql_int">

        )<cfif listPosition lt listLen(form_game_uid)>,</cfif>

        </cfloop>

    </cfquery>

    WolfShade
    WolfShade
    Brainiac
    September 19, 2018

    What flavour of database are you using?  MS-SQL, Oracle, MySQL?

    V/r,

    ^ _ ^

    A
    ACS LLCAuthor
    Inspiring
    September 19, 2018

    MS-SQL 2008

    WolfShade
    WolfShade
    Brainiac
    September 19, 2018

    According to this article, you can:

    INSERT INTO tableA (col1, col2, col3)

    VALUES (1,'a','x'), (2,'b','y'), (3,'c','z')

    So, theoretically, if the list order is in the proper order as the query, you can run the select query and get the data, add a column to the query object for the list data, and:

    <cfset iteration=1 />
    INSERT into tableA (col1,col2,col3)
    VALUES
    <cfloop list="#yourList#" index="idx">
         (queryname.col1[iteration],'#idx#', queryname.col3[iteration])<cfif iteration lt listLen(yourList)>, </cfif>
    <cfset iteration++ />
    </cfloop>

    HTH,

    ^ _ ^

      WolfShade
      WolfShade
      Brainiac
      September 19, 2018

      https://forums.adobe.com/people/ACS+LLC  wrote

      At the moment I am using a CFLOOP to execute multiple queries which I know is not an efficient way to do it.

      I wish more developers took that line of reasoning.  I see code all the time where the developer iterates a loop with a CFQUERY inside.  (CRINGE!)

      https://forums.adobe.com/people/ACS+LLC  wrote

      However, I am a little stuck, because one of the values, let's say for arguments sake it's SourceField_2 will not come from the SELECT query from the destination table, it is actually a value which is part of a submitted list that I am looping over.

      Well, once you've got your SELECT query, you can modify it by adding a column to it, then iterate the query inserting the list elements into it (assuming the recordset order matches the list order), and then do the INSERT SELECT.  Just a thought.

      HTH,

      ^ _ ^

      PS.  Actually, now that I think about it.. I wonder if there's a way to convert the list into a query and just append it to the SELECT query.

      PPS.  Okay, I just realize that didn't sound right.  Been a long day, and I just dumped a soda over in my car on my way back from lunch.  (facepalm)  Let me think about this, some more.

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