How to Create a Copy of a Row in SQL

You might require to grab an existing record quickly and create a copy of it. This tutorial on how to create a copy of a row in SQL might come in handy in those situations.

The syntactic command to do that would be:

INSERT INTO TableName (id, def, desc)
SELECT <newId>, def, desc FROM TableName WHERE id = <oldId>

where replace TableName with the name of your table where the action is being performed.
where replace <newId> with the new Id you want to give to your record.
where oldId would be the id of the record you want to create a copy of.

NOTE: I have taken just three columns, you can add as many as you want depending upon the table you are working on. It is just for the sake of explanation.

Example for Creating a Copy of a Row in SQL

An example would be let’s say my table name is ‘Books’ with two columns Id and Name.

The record that I am trying to duplicate is this:

where Id of the record is 1 and the name of the Book is ‘Coraline‘.

Now if I want to create a copy of this record, the only thing I want to change is the Id.

Since we are inserting a new record, we will be using the SQL INSERT statement.

So my newId would be 2 or any number in this case. So I will start it off by writing:

INSERT INTO BOOKS (Id, Name)

We are not done yet. Now we have to provide a condition as to what needs to be inserted.

Follow it by:

SELECT 2, Name

Here you are not going to write Coraline all over again, coz SQL would automatically replace the name with ‘Coraline’ once you provide the full condition where you mention the Id.

So the next thing would be to provide where you are looking for. It is the same table right? So you are going to mention that next.

SELECT 2, Name FROM BOOKS

We are still missing the condition. Time to add that.

We are selecting a record from the table called ‘BOOKS’, but which record?

Hence, the following line:

SELECT 2, Name FROM BOOKS WHERE Id = 1

So my final command would look like this:

INSERT INTO BOOKS (Id, Name)
SELECT 2, Name FROM BOOKS WHERE Id = 1

Now when you run the query, you will get the following:

In case you have more than two columns, remember you need to put the names of all the columns that you want duplicated in the brackets in the INSERT line and then include the column names to be duplicated in the next SELECT line.

For example:

INSERT INTO BOOKS (Id, Column1, Column2, Column3, Column4 and so on....) 
SELECT 2, Column1, Column2, Column3, Column4, and so on... FROM BOOKS WHERE Id = 1

For those which you don’t want to duplicate and want to provide your own value, just add your value instead of the column name there.

For example:

INSERT INTO BOOKS (Id, Column1, Column2, Column3, Column4 and so on....)  
SELECT 2, Column1, Column2, 'Value I Provided', Column4, and so on... FROM BOOKS WHERE Id = 1

where I just replaced the value of Column3 with my own value.

That’s it! We are done.

If you like the above tutorial on How to Create a Copy of a Row in SQL, please watch this space to find out more SQL tutorials.