cPanel Question posted in
The official documentation can be seen here.

How to add sequential numbers to an empty SQL table – CPanel

I’m trying to add sequential numbers, from 1 to 200M, to an empty SQL table. The number is too large to make the list with Excel. I’m also using Cpanel which has a low file size limit. Any way to do this using an sql query? (Note: other posts have given sample code for this, but they don’t seem to be standard SQL queries that can be used in cpanel.)

Any advice will be appreciated.

Tags:

2

Answers


  1. 0

    The easiest way is to use information_schema.columns view.
    Simple SQLFiddle demo for 100.000 rows (only 100 thousand since I don’t want to kill SQLFiddle) – it takes only a few seconds:

    SELECT @x:=@x+1
FROM (SELECT @x:=0) xx,
information_schema.columns a,
information_schema.columns b,
information_schema.columns c,
information_schema.columns d
LIMIT 10000;

    If your database has, say, 100 columns, then the cross join of 4 tables can give 100^4 = 100000000 potential rows, this is much more than you need.

    Login or Signup to reply.
  2. 0

    An iterative solution would be:

    BEGIN
DECLARE @Counter int=0
WHILE @Counter <= 2000000
    BEGIN
    INSERT tableName(columnName)
    VALUES (@Counter)
    SET @Counter=@Counter+1
    END
END

    The drawback to this solution is that it will execute 2 million insert statements against your database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search