Example SQL script to create rows based on table data.

example scripts for Dominic. I am sharing because it's something I am doing I can share. Not something I can do very often. Problem: based on one table, create data in another.

DROP TABLE IF EXISTS `tags`;
CREATE TABLE `tags` (
  `uniq` int(10) NOT NULL AUTO_INCREMENT,
  `startnum` int(10) not null default '0',
  `endnum` int(10) not null default '0', 
  `assignedto` varchar(20) default '', 
  `parsed` int(1) default '0',
  `lastmod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uniq`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `asstags`;
CREATE TABLE `asstags` (
  `uniq` int(10) NOT NULL AUTO_INCREMENT,
  `tag` int(10) not null default '0',
  `assignedto` varchar(20) default '',
  `lastmod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uniq`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into tags (startnum,endnum,assignedto) values ('0','49','Fred') ; 
insert into tags (startnum,endnum,assignedto) values ('50','99','Barney') ; 
insert into tags (startnum,endnum,assignedto) values ('100','149','Wilma') ; 
insert into tags (startnum,endnum,assignedto) values ('150','199','Betty') ; 

DROP PROCEDURE IF EXISTS foralltags;
DROP PROCEDURE IF EXISTS CREATEASSTAGS;
DELIMITER ;;
CREATE PROCEDURE CREATEASSTAGS()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE e INT DEFAULT 0;
DECLARE u INT DEFAULT 0;
DECLARE p varchar(20) default '';
SELECT startnum FROM tags where parsed = 0 limit 1 INTO i;
SELECT endnum FROM tags where parsed = 0 limit 1 INTO e;
SELECT assignedto FROM tags where parsed = 0 limit 1 INTO p;
SELECT uniq FROM tags where parsed = 0 limit 1 INTO u;
set e = e + 1 ; 
WHILE i<e DO 
  INSERT INTO asstags (tag, assignedto) VALUES (i,p) ;
  SET i = i + 1;
END WHILE;
update tags set parsed = '1' where uniq = u ; 
End;
CREATE PROCEDURE foralltags()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE z INT DEFAULT 0;
SELECT count(uniq) FROM tags where parsed = 0 limit 1 INTO n;
SET z=0;
WHILE z<n DO 
  CALL createasstags();
  set z = z + 1 ; 
END WHILE;
End;;;
CALL foralltags() ; 
select * from asstags ; 
select * from tags ;