Example SQL script to create rows based on table data.
1000-01-01 by: login
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 ;