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

[ACS LLC]

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

[WolfShade]

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.

[WolfShade]

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

V/r,

^ _ ^

[ACS LLC]

MS-SQL 2008

[WolfShade]

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,

^ _ ^

[ACS LLC]

That looks like it will do the trick!

I am just about to wrap up after an extremely long day so I'd prefer to get a fresh set of eyes on it before I start to potentially break the code

I'll try and integrate it in the morning and report back! It certainly looks like it will work!

Thanks

Mark

[WolfShade]

Actually, I just realized that since the list data isn't actually going into the query, you can forget about the part of adding a column to the query object.  D'OH!  It's going into a table.

Like I said.. long day..

V/r,

^ _ ^

[ACS LLC]

I actually just finished after getting bogged down with another issue

I did take a quick look at the code again but I'm I little confused about how to integrate the select of the initial data. Perform a query first?

It's a shame I can't do a 'MOVE' of the data while at the same time somehow dropping in that list value.

I'll review tomorrow, it's a little late here now.

[WolfShade]

The list needs to be matched to the query.  You can either add the column to the SELECT table and insert the list values before processing and use SELECT INTO to copy the data over, or you can run the SELECT query and iterate the list as I have suggested (which, granted, it's two separate queries, but it's a LOT more efficient than placing a query inside a loop.)

V/r,

^ _ ^

[WolfShade]

But, then again, maybe I'm not thinking outside the box.  But it seems to me that SELECT INTO will only work with database values, not an external list or collection.  AFAIK, if you want to use SELECT INTO, then the list data needs to be integrated into the database, first.

Again, I may be missing something.  Someone else might see this and go, "Oh, hey, I have an idea."  But to my knowledge, you can't mix a SELECT and an external list or array or collection for a SELECT INTO query.

V/r,

^ _ ^

[WolfShade]

The only other thing that I can think of, and it will still use two queries but the first one will be an INSERT, is to create another table just for the UIDs, INSERT the UIDs into that table and associate them with a record (or some records, depending) in the other table, and do a INSERT INTO where the SELECT is using a LEFT OUTER JOIN to that second table.  That should speed things up, a bit.

HTH,

^ _ ^

[Dave Watts]

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

[ACS LLC]

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

[WolfShade]

Fifty records doesn't sound like it will be too much, unless you're moving recordsets that contain, like, 100 columns of data and/or large binaries.    It should process quite quickly.

V/r,

^ _ ^

[ACS LLC]

No only tiny bits of info, about 4 different values, all INTS, so nothing much

There is a risk that there could be a large amount of people using it within a very small time frame, maybe 10,000 to 20,000 in the same day, with a potential for a fair amount of concurrent use, so I wanted to make sure I've done my part to ensure the code holds up and is reasonably efficient

[WolfShade]

Are the UIDs procedurally generated?  Or options that can be selected by the users and submitted?

V/r,

^ _ ^

[ACS LLC]

The data that I am adding in is from a selection made by the end user. There's a table that has individual 'credits' that they have, so say they have 50 credits, 50 records exist but each record can contain a value that is unique to that credit.

They use the credits to spend on selected products, the credits are used in the order in which they were originally added to the account, based on a UID that is assigned to each credit. So if they spend 10 credits on 10 items, the oldest 10 credits are used.

The credits are deleted and the data is moved into a new table, but there's an extra value, the product ID used for each credit, creating 10 new records in the user products table.

[WolfShade]

AH!  Okay.. then the UIDs already exist in the database.  It would be easier (and you could use the INSERT INTO command) if you grabbed those UIDs from that table before deleting them.  You wouldn't have to create a list and try to collate that into the query, manually.  My earlier suggestion of a LEFT OUTER JOIN is perfect for this scenario, if used prior to deleting the UIDs from the other table.

You should place all this inside a CFTRANSACTION to prevent orphaned data if anything goes wrong during the process.

Just a thought.

V/r,

^ _ ^

[ACS LLC]

The list of UID's that I am putting into the mix are based on manually selected list from a form post on the previous paste, which is why I am using the loop to build the query up.

I think I'm going to have to stick with looping and building a single query that sends the whole pay load in one go.

[ACS LLC]

I was thinking of a possible query over query, but I think I'll use more resources building it all together with the injection of that extra data that actually just using a cfloop to build a single query for all the data

[WolfShade]

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

I think I'm going to have to stick with looping and building a single query that sends the whole pay load in one go.

Personally, I think this is a mistake.  But you're the one coding it and will have to maintain it.  Best of luck.

V/r,

^ _ ^

[ACS LLC]

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>

[BKBK · Accepted solution]

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>

[ACS LLC]

I managed to get around to switching to the code above, worked great. About 3 milliseconds to add 37 individual records, when previously it was taking 1 to 3 per record.

Thanks!