Recently, as component of a replication process, I needed to replicate a lot of rows in one table while altering among the column worths (believe, the theoretical “international trick” column). To do this, I utilized MySQL’s INSERT INTO SELECT phrase structure which gives 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 consists of a listID, which is a referral to the moms and dad checklist:
PRODUCE TABLE 'todo_item' (
' id' int( 10) anonymous NOT NULL AUTO_INCREMENT,
' listID' int( 10) anonymous NOT NULL,-- The "international trick" recommendation.
' summary' varchar( 300) NOT VOID,
' createdAt' datetime NOT NULL,
' dueAt' datetime DEFAULT NULL,
' type' int( 11) NOT VOID,
MAIN SECRET (' id'),.
SECRET 'byList' (' listID').
) ENGINE= InnoDB DEFAULT CHARSET= utf8;.
KEEP IN MIND: To be clear, I am describing the
listIDcolumn as the “international trick”, yet this is simply from a theoretical perspective – I do not in fact make use of international crucial restrictions in my ColdFusion applications due to the fact that it often tends to make data source movements a lot more difficult (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 making use of the freshly produced
listID
For brevity, I’m just mosting likely to reveal the 2nd action. Take into consideration the adhering to ColdFusion information gain access to things (DAO) which gives a copyItemsIntoList() technique. This technique takes the resource ID of the initial checklist as well as the target ID of the freshly produced checklist (from action 1 over):
part.
outcome = incorrect.
tip="I give data-access approaches for todo listings.".
{
/ **.
* I duplicate the checklist things from the resource ToDo Listing right into the target ToDo Listing.
*/.
public space 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,.
type.
)(.
/ **.
* As component of the replication procedure, we wish to maintain just the same worths.
* besides the 'listID', which is being transformed to indicate the freshly.
* produced 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. As well as, when we invoke this ColdFusion technique as well as take a look at our demonstration table, we see the following:
As you can see, the 3 rows connected with the resource checklist (ID: 1) have actually been copied as well as connected with the target checklist (ID: 2).
I might have reviewed every one of the resource rows right into the ColdFusion application runtime and after that carried out a variety of INSERT declarations - that would certainly have provided me extra versatility in exactly how I took care of the information. However, in this instance, there had not been truly any kind of requirement for information makeover - the INSERT INTO SELECT phrase structure for MySQL sufficed for my use-case.
Intend to make use of code from this message?
Have a look at the certificate
