Using INSERT INTO SELECT To Duplicate Rows In MySQL And Lucee CFML

Last week, as part of a duplication workflow, I had to duplicate a bunch of rows in one table while changing one of the column values (think, the conceptual "foreign key" column). To do this, I used MySQL's INSERT INTO SELECT syntax which provides a bulk INSERT API that is powered by a SELECT statement. I'd never actually done this in a production application before; so, I thought it might be worth a quick MySQL and ColdFusion demo.

For the sake of the demo, consider this MySQL table that contains "items" in a ToDo list. Each row contains a listID, which is a reference to the parent list:

CREATE TABLE `todo_item` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`listID` int(10) unsigned NOT NULL, -- The "foreign key" reference.
	`description` varchar(300) NOT NULL,
	`createdAt` datetime NOT NULL,
	`dueAt` datetime DEFAULT NULL,
	`sort` int(11) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `byList` (`listID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

NOTE: To be clear, I am referring to the listID column as the "foreign key", but this is merely from a conceptual standpoint - I don't actually use foreign key constraints in my ColdFusion applications because it tends to make database migrations much more challenging (among various other reasons).

If I needed to duplicate a ToDo List within my ColdFusion application, the duplication process would take place in two operations, likely contained within a single transaction:

1. Create a new ToDo List row.

2. Copy all the ToDo List Item rows from the old list into the new list using the newly generated listID.

For the sake of brevity, I'm only going to show the second step. Consider the following ColdFusion data access object (DAO) which provides a copyItemsIntoList() method. This method takes the source ID of the first list and the target ID of the newly generated list (from step 1 above):

component
	output = false
	hint = "I provide data-access methods for todo lists."
	{

	/**
	* I copy the list items from the source ToDo List into the target ToDo List.
	*/
	public void function copyItemsIntoList(
		required numeric sourceListID,
		required numeric targetListID
		) {

		```
		<cfquery name="local.results" result="local.metaResults">
			/* DEBUG: listGateway.copyItemsIntoList(). */
			INSERT INTO todo_item
			(
				listID,
				description,
				createdAt,
				dueAt,
				sort
			)(

				/**
				* As part of the duplication process, we want to keep all the same values
				* EXCEPT for the `listID`, which is being changed to point to the newly
				* created ToDo list.
				*/
				SELECT
					<cfqueryparam value="#targetListID#" sqltype="bigint" />,
					i.description,
					i.createdAt,
					i.dueAt,
					i.sort
				FROM
					todo_item i
				WHERE
					i.listID = <cfqueryparam value="#sourceListID#" sqltype="bigint" />
				ORDER BY
					i.sort ASC

			);
		</cfquery>
		```

	}

}

As you can see, within a single operation, we're SELECTing all of the rows from one list and we're INSERTing them into another list. And, when we invoke this ColdFusion method and look at our demo table, we see the following:

As you can see, the three rows associated with the source list (ID: 1) have been duplicated and associated with the target list (ID: 2).

I could have read all of the source rows into the ColdFusion application runtime and then executed a number of INSERT statements - that would have given me more flexibility in how I handled the data. But, in this case, there wasn't really any need for data transformation - the INSERT INTO SELECT syntax for MySQL was sufficient for my use-case.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/4370