Copying a row in the same table without having to type the 50+ column names (while changing 2 columns)

Question

During my job, I usually have to copy rows while changing their primary key and giving them a new stamp and maybe changing the foreign key.

The problem is I don't want to type all the column names while doing;

insert into table_name
select pk_seq.nextval, 
       'foreign-key', 
       col3,
       col4...col51
  from table_name
 where pk_id = "original_primary_key"

And if i do * in the select statement i won't be able to update the first 2 columns...

Is there any way to do how I want to do it?

Answer 1

Sorry - it's an all or nothing affair.
There isn't anything between SELECT * and list the specific columns, it's one or the other.

Answer 2

How about creating a stored procedure that takes the original primary key in parameter, builds the insert query dynamically and executes it

Answer 3

Well it may not be much less verbose, but this PL/SQL is an option:

begin
  for r in (select *
              from table_name
             where pk_id = 'original_primary_key')
  loop
    r.pk := pk_seq.nextval;
    r.fk := 'foreign-key';
    insert into table_name values r;
  end loop;
end;

Answer 4

You could make a simple stored procedure that take a table name and using the data dictionary writes the select statement text for you (the text of the select). Then copy, paste and Modify.

Answer 5

Based on Tony's answer:

We know that at most one row will be returned since we are searching on primary key. And assuming that a valid key value is specified, at least one row will be returned. So we don't need the loop:

declare
    r table_name%ROWTYPE;
begin
    select *
    into r
    from table_name
    where pk_id = "original_primary_key";
-- 
    select pk_seq.nextval into r.pk_id from dual;
     -- For 11g can use instead: r.pk_id := pk_seq.nextval;
    r.fk_id := "new_foreign_key";
    insert into table_name values r;
end;

Answer 6

You could just query the data dictionary to generate the SQL for you.

SELECT 'tbl.' || column_name || ','
FROM   user_tab_columns
WHERE  table_name = 'MYTABLE'
ORDER BY column_id;

Get the result of this query, paste into your SQL statement, adapt as necessary, and voila.

Answer 7

You can create a 'temp' table, update two columns and do an insert-select from this 'temp' table.

Eaxmple:

create table temptemp as 
select *
  from table_name
 where pk_id = "original_primary_key"

update temptemp
set col1 = ...
,   col2 =....

insert into table2
select * from temptemp;