« News only nerds would care about... | Main | Where's My Data? »
March 21, 2005
Copying a really long row in SQL
Programmers are typically lazy when it comes to coding and therefore do not like to type more than they have to.
Every once in a while, I need to repeat the entire contents of a very long database row but change just a few fields like the primary key and description. In other words, I need to copy the row and append it at the end of the table after making a few minor changes. Here is a method to allow you to easily do this without typing INSERT INTO longtable (field1, field2, field3, etc.) VALUES (value1, value2, value3, etc.);
--create a temporary table to copy the long row into
create global temporary table tmpLongTable as select * from longtable;
--copy the row into the temporary table
insert into tmpLongTable (select * from longtable where primary_id = 23);
--update the temporary table to reflect the new data
update tmpLongTable set primary_id = 26, description_txt = 'New Description' where primary_id = 23;
--insert the updated copied row back into the main table
insert into longtable (select * from tmpLongTable);
--drop the temporary table - not needed, but better safe than sorry
drop table tmpLongTable;
--see if everything worked
select * from longtable where primary_id = 26;
--your database supports transactions, right?
commit;
I have tested this in both Oracle and DB2. Another way to accomplish this is: INSERT INTO mytable (select 'myvalue1', 'myvalue2', FIELD3, etc. from mytable); I personally like the first way better.
Posted by Chuck at March 21, 2005 04:00 PM
Trackback Pings
TrackBack URL for this entry:
http://www.chuckcaplan.com/blog/mt-tb.cgi/16
Comments
--insert the updated copied row back into the main table
insert into longtable (select * from tmpLongTable);
in the insert statements, the parenthesis bring up an error, which can be resolved by removing them.
Posted by: Barkın Aygün at June 28, 2006 07:19 AM