I thought that cloning a database record should be easy until I decided to write a query for it.

I then realized that there is no simple query which clones a record.

The steps though are pretty simple:
1. Select the record you want to clone
2. Insert the values in the table

Example
Lets consider you have an events management database where the primary key is the eventid and you would like to clone the events frequently

First of all, here is the query you would use to select the event you want to clone

Now let’s clone the record

Thus, we have combined INSERT and SELECT query in one query.

But there is a problem in this query. When you try running this query, you might get a “duplicate key” error. That’s because the record clones everything including the primary key which is the eventid.

To avoid that, we need to change our steps as follows
1. Create a temporary table
2. Select the record to be cloned and store it in a temporary table
3. Update the primary key in the temporary table so that its not duplicate
4. Insert the record from the temporary table into the main table
5. Drop temporary table

And here’s the query

 

Leave a Reply

Your email address will not be published. Required fields are marked *