Re: Using Cursors to fetch from one table and insert into another table

[Bob Field]

Generally the easiest and most efficient way to insert data into one table from another is to use the INSERT INTO tablename (column-list) SELECT ... syntax, unless there is complex logic involved that that statement cannot accomodate.

delimiter $$
create procedure copy_data () begin
declare done tinyint default 0;
declare c1 cursor for select fa, fb from table1;
open c1;
loop1: loop begin
declare vfa int;
declare vfb varchar(20);
declare continue handler for sqlstate '02000' set done = 1;
fetch c1 into vfa, vfb;
if done then
leave loop1;
end if;
insert into table2 (fa, fb) values (vfa, vfb);
end; end loop;
close c1;
end$$
delimiter ;

Note that this procedure is exactly equivalent to the following:

INSERT INTO table2 (fa, fb) SELECT fa, fb FROM table1;