How can I copy a record, changing only the id?

Question

My table has a large number of columns. I have a command to copy some data - think of it as cloning a product - but as the columns may change in the future, I would like to only select everything from the table and only change the value of one column without having to refer to the rest.

Eg instead of:

INSERT INTO MYTABLE (
SELECT NEW_ID, COLUMN_1, COLUMN_2, COLUMN_3, etc
FROM MYTABLE)

I would like something resembling

INSERT INTO MYTABLE (
SELECT * {update this, set ID = NEW_ID}
FROM MYTABLE)

Is there a simple way to do this?

This is a DB2 database on an iSeries, but answers for any platform are welcome.

Answer 1

You could do this:

create table mytable_copy as select * from mytable;
update mytable_copy set id=new_id;
insert into mytable select * from mytable_copy;
drop table mytable_copy;

Answer 2

I don't think this is doable entirely within SQL without going to the trouble of creating a temp table. Doing it in memory should be much faster. Beware if you go the temporary table route that you must choose a unique name for your table for each function invocation to avoid the race condition where your code runs twice at the same time and mangles two rows of data into one temp table.

I don't know what kind of language you're using but it should be possible to obtain a list of fields in your program. I would do it like this:

array_of_field_names = conn->get_field__list;
array_of_row_values = conn->execute ("SELECT... ");
array_of_row_values ["ID"] = new_id_value
insert_query_string = "construct insert query string from list of field names and values";
conn->execute (insert_query_string);

Then you can encapsulate that as a function and just call it specifying table, old id and new id and it'd work it's magic.

In Perl code the following snippet would do:

$table_name = "MYTABLE";
$field_name = "ID";
$existing_field_value = "100";
$new_field_value = "101";

my $q = $dbh->prepare ("SELECT * FROM $table_name WHERE $field_name=?");
$q->execute ($existing_field_value);
my $rowdata = $q->fetchrow_hashref; # includes field names
$rowdata->{$field_name} = $new_field_value;

my $insq = $dbh->prepare ("INSERT INTO $table_name (" . join (", ", keys %$rowdata) . 
    ") VALUES (" . join (", ", map { "?" } keys %$rowdata) . ");";
$insq->execute (values %$rowdata);

Hope this helps.