Xophmeister's World

Occasionally, one needs to duplicate a table record; either in the same table, or into another table (e.g., a history or audit table). Of course, when copying data into the same table, you will need to modify a few fields to avoid breaking any uniqueness constraints.

In vanilla Oracle SQL, this can be done like so:

insert into myTable (id, description, cost)
values      select id + 1,
                   description,
                   150
            from   myTable
            where  id = 123;

Fine. However, I’m a lazy programmer and what happens if there are 50 fields in your table and you only need to change two of them? Even in the above example, having three fields is pushing it!

One approach would be to create a temporary table (CTAS) and then insert from there:

create table temp as select *
                     from   myTable
                     where  id = 123;

update temp set id   = 124,
                cost = 150;

insert into myTable values select * from temp;

drop table temp;

What a pain in the arse! Moreover, DDL commands like create and drop will commit your transaction, which isn’t necessarily what you want. So here’s an alternative, using PL/SQL:

declare
  myRecord myTable%rowtype;
begin
  select * into myRecord
  from   myTable
  where  id = 123;

  myRecord.id   := 124;
  myRecord.cost := 150;

  insert into myTable values myRecord;
end;

Sorted! Obviously you incur some penalty from using a PL/SQL block, but it’s negligible compared to the saving in coding and probably equivalent to the CTAS approach (but without the implicit commit). Moreover, clearly, by only changing the field values that you need to, it mitigates against programmer error: So lazy is good!