2

I need to create a temp table in MySQL that simply holds a list of sequential numbers (up to x, specified by me at creation time). Simply:

enter image description here

I would think this would be simple to do and useful in several cases, like when you need to join with such a table to multiply a set of records. Does anyone know a simple way to achieve this?

TIA.

Drew
  • 24,851
  • 10
  • 43
  • 78
dhalia
  • 401
  • 5
  • 21
  • 2
    Are you using `mysql` or `oracle`? This question has most likely already been answered though regardless... http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql or http://stackoverflow.com/questions/2847226/sql-to-generate-a-list-of-numbers-from-1-to-100 – sgeddes Aug 20 '16 at 00:42
  • I highly suggest just creating a permanent helper table. You could even piggy back a few helper tables all into one. No need to endure the overhead of DDL routines for this. You will find them useful with `LEFT JOIN`s in dates (dates a worker was absent). That sort of thing. – Drew Aug 20 '16 at 01:09
  • I wrote up a MyISAM answer [HERE](http://stackoverflow.com/a/33666394). The reason it was not innodb was well, it didn't really matter that it was MyISAM, and the typical INNODB id range gap anomoly would not surface and thus throw off the id ranges (gaps = bad for this). INNODB is a workhorse that protects you, but for this, at least in creation, it would generate range gaps due to the uncertainty of sizes on each from-self insert. After the table is created, you can always convert it to innodb. Also, I have written up helper tables for all date +- 20 years for sales reports etc (LEFT JOINS) – Drew Aug 20 '16 at 01:14
  • http://datacharmer.blogspot.co.uk/2006/06/filling-test-tables-quickly.html?m=1 – Strawberry Aug 20 '16 at 03:39

0 Answers0