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

Your example should almost work. Just add the column names of the new table to it.

INSERT INTO MYTABLE
(id, col1, col2)
SELECT new_id,col1, col2
FROM TABLE2
WHERE ...;

Answer 2

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 3

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.

Answer 4

Ok, try this:

declare @othercols nvarchar(max);
declare @qry nvarchar(max);

select @othercols = (
select ', ' + quotename(name)
from sys.columns
where object_id = object_id('tableA')
and name <> 'Field3'
and is_identity = 0
for xml path(''));

select @qry = 'insert mynewtable (changingcol' + @othercols + ') select newval' + @othercols;

exec sp_executesql @qry;

Before you run the "sp_executesql" line, please do "select @qry" to see what the command is that you're going to run.

And of course, you may want to stick this in a stored procedure and pass in a variable instead of the 'Field3' bit.

Rob

Answer 5

hi, i've never worked with db2 but in mssql you could solve it with following procedure. this solution only works if you dont care what new id the items get.

1.) create new table with same scheme but where the id column incrementes automatically. (mssql "identitity specification = 1, identity increment = 1)

2.) than a simple

insert into newTable(col1, col2, col3)
select (col1, col2, col3) from oldatable

should be enough, be sure not to include your id colum in the above statement