Jul 29, 2010

How to Create a Tally Table in MySQL

A tally table consists of a single field that simply counts up. The following SQL will create the table and add 10,000 rows.

-- Create the tally table.
CREATE TABLE tally (
	id int unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id)
);

-- Populate it
delimiter //
CREATE PROCEDURE create_tally()
BEGIN
  	SET @x = 0;
  	WHILE @x<100000 DO
  		--Insert a single row into tally.
  		INSERT INTO tally (id) VALUES (NULL);
		SET @x = @x + 1;
	END WHILE;
END//
delimiter ;
CALL create_tally();
DROP PROCEDURE create_tally;