When working with Oracle databases, it is sometimes interesting to create a duplicate row of a table. Most of the times this happens when I am testing something on an application and need to tweak one of the entries in a table.

There is a standard SQL trick to duplicate elements of a table that can be used in an Oracle database. I am showing this here because it can be of interest for other people that have the experience of working in an Oracle environment.

The basic idea behind this technique is just to create a temporary table that will hold the data only during the time necessary to make the required modifications. Since the table is created immediately, the inserted content will make the table have all columns of the original table.

Suppose that the following returns non null:

select * from myTable where myKey=8572

Then the required code looks something like the following:

EXECUTE IMMEDIATE
CREATE TABLE temp_table AS SELECT * FROM myTable where myField=8572
UPDATE temp_table SET anotherField='AA'
INSERT INTO quote SELECT * FROM temp_table
DROP TABLE temp_table

With this simple trick one can duplicate any row and modify it to hold the required values.

Further Reading

A comprehensive book on Oracle is Oracle Essentials by Greenwald et al. Here is another Oracle book that is worth reading to get a better idea of how it works.

Similar Posts:

• Accessing MySql From SBCL
• Is Using Hash Tables a Good Thing?
• How to avoid NULL pointers
• Using Closures in Java
• The Way a Program Starts in Windows