How to copy a record, changing only the identifier?

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 since the columns may change in the future, I would only like to select everything from the table and change only the value of one column, without resorting to the rest.

For example, instead of:

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

I would like something like

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

Is there an easy way to do this?

This is a DB2 database on iSeries, but responses to any platform are welcome.

Answer 1

You can do it:

 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 fully doable in SQL without creating the problematic temp table. Doing this in memory should be much faster. Beware if you go along the temporary route of the table, so that you choose a unique name for your table for each function call, to avoid a race condition when your code runs twice at the same time and manages two rows of data in one temporary table.

I do not know which language you are using, but it should be possible to get 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 this as a function and simply call it by specifying a table, an old identifier and a new identifier, and this will work the magic.

In Perl code, the following snippet will 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 3

Ok, try the following:

 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 running the line "sp_executesql", run "select @qry" to find out what the command should execute.

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

Rob

Answer 4

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 5

I have never worked with db2, but in mssql you could solve it using the following procedure. this solution only works if you don't like which new identifier gets.

1.) create a new table with the same schema, but where the id column automatically grows. (mssql "= 1, increment of identity = 1)

2.) than simple

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

should be enough, do not forget to specify your id colum in the above expression