Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Shawn Grigson
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Shawn Grigson ( @shawngrig )

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

By on
Tags: ,

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:

MySQL GUI showing that the three rows associated with the source list have been duplicated, with their listID columns changed to point to the target list.

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.

Reader Comments

Post A Comment — I'd Love To Hear From You!

Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel