Recently, as component of a replication operations, I needed to replicate a number of rows in one table while transforming among the column worths (assume, the theoretical “international secret” column). To do this, I made use of MySQL’s INSERT INTO SELECT phrase structure which supplies a mass INSERT API that is powered by a SELECT declaration. I would certainly never ever in fact done this in a manufacturing application prior to; so, I believed it may be worth a fast MySQL as well as ColdFusion demonstration.
For the demonstration, consider this MySQL table which contains “things” in a ToDo checklist. Each row includes a listID, which is a referral to the moms and dad checklist:
DEVELOP TABLE 'todo_item' (
' id' int( 10) anonymous NOT NULL AUTO_INCREMENT,
' listID' int( 10) anonymous NOT NULL,-- The "international secret" recommendation.
' summary' varchar( 300) NOT VOID,
' createdAt' datetime NOT NULL,
' dueAt' datetime DEFAULT NULL,
' kind' int( 11) NOT VOID,
MAIN TRICK (' id'),.
TRICK 'byList' (' listID').
) ENGINE= InnoDB DEFAULT CHARSET= utf8;.
KEEP IN MIND: To be clear, I am describing the
listIDcolumn as the “international secret”, yet this is just from a theoretical point ofview – I do not in fact make use of international vital restraints in my ColdFusion applications due to the fact that it has a tendency to make data source movements a lot more tough (amongst numerous other factors).
If I required to replicate a ToDo Listing within my ColdFusion application, the replication procedure would certainly occur in 2 procedures, most likely consisted of within a solitary purchase:
-
Produce a brand-new ToDo Listing row.
-
Replicate all the ToDo Listing Product rows from the old checklist right into the brand-new checklist utilizing the freshly created
listID
For brevity, I’m just mosting likely to reveal the 2nd action. Think about the complying with ColdFusion information accessibility item (DAO) which supplies a copyItemsIntoList() approach. This approach takes the resource ID of the initial checklist as well as the target ID of the freshly created checklist (from action 1 over):
element.
outcome = incorrect.
tip="I give data-access techniques for todo listings.".
{
/ **.
* I replicate the checklist things from the resource ToDo Listing right into the target ToDo Listing.
*/.
public gap feature copyItemsIntoList(.
needed numerical sourceListID,.
needed numerical targetListID.
) {
"'.
<< cfquery name=" local.results" outcome=" local.metaResults">
>/ * DEBUG: listGateway.copyItemsIntoList(). */.
PLACE right into todo_item.
(.
listID,.
summary,.
createdAt,.
dueAt,.
kind.
)(.
/ **.
* As component of the replication procedure, we wish to maintain just the same worths.
* with the exception of the 'listID', which is being transformed to indicate the freshly.
* developed ToDo checklist.
*/.
SELECT.
<< cfqueryparam worth=" #targetListID #" sqltype=" bigint"/>>,.
i.description,.
i.createdAt,.
i.dueAt,.
i.sort.
FROM.
todo_item i.
IN WHICH.
i.listID = << cfqueryparam worth=" #sourceListID #" sqltype=" bigint"/>>.
ORDER BY.
i.sort ASC.
);.
<.
"'.
}
}
As you can see, within a solitary procedure, we're SELECT ing every one of the rows from one checklist as well as we're INSERT ing them right into one more checklist. And also, when we invoke this ColdFusion approach as well as consider our demonstration table, we see the following:
As you can see, the 3 rows related to the resource checklist (ID: 1) have actually been copied as well as related to the target checklist (ID: 2).
I might have checked out every one of the resource rows right into the ColdFusion application runtime and after that performed a variety of INSERT declarations - that would certainly have provided me extra versatility in just how I took care of the information. Yet, in this instance, there had not been actually any kind of requirement for information improvement - the INSERT INTO SELECT phrase structure for MySQL sufficed for my use-case.
Intend to make use of code from this blog post?
Have a look at the permit
