delimiter //
create procedure migrate_url_count_data()
begin
declare current_url_id, current_url_count_id bigint;
declare u char(150);
declare uh char(32);
declare si, code smallint;
declare st datetime;
declare h, c bigint;
declare cur1 cursor for select url, url_hash, customer_site_id, stat_time, hits, completes from url_count;
declare cur2 cursor for select url_hash, customer_site_id, stat_time, hits, code from url_count;
declare exit handler for not found begin end;
open cur1;
loop
fetch cur1 into u, uh, si, st, h, c;
begin
declare continue handler for not found
begin
set current_url_id = -1;
end;
-- create a row in url if it doesn't exist already, and get the row's id
select id from url where hash=uh into current_url_id;
if current_url_id = -1 then
insert into url (url, hash) values (u, uh);
set current_url_id = last_insert_id();
insert into migration_log (info) values (concat('inside if: ',current_url_id));
end if;
insert into migration_log (info) values (current_url_id);
end;
insert into url_count2 (site_id, url_id, stat_time, hits, completes) values (si, current_url_id, st, h, c);
end loop;
close cur1;
end;
//
-- looking at migration_log, as well as comparing the migrated and unmigrated data, last_insert_id() only changes after the loop ends