Geek asks and answers

How to insert a sequence of rows into a MySQL database

I have an existing table with which I would like to do the equivalent of the following:

insert into the_table set col0=1, col1=0, col2=0;
insert into the_table set col0=2, col1=0, col2=0;
insert into the_table set col0=3, col1=0, col2=0;
...
...
insert into the_table set col0=255, col1=0, col2=0;

Because this is what I will do interactively over and over again, I would like to know how to reduce it to one operator and stuff it through the console so often.

EDIT:

Typing text on the console is not realistic:

insert into the_table (col1, col2, col3) values (0,0,0),(1,0,0)....(255,0,0);

Thank.

+3
source share
4 answers

A bit hacked, and technically these will be two requests:

SET @i:=0;
INSERT INTO the_table (col0,col1,col2)
SELECT @i:=@i+1,0,0 FROM SomeTable LIMIT 255;

Where SomeTableis any table in your database that has at least 255 rows.

+2
source 
insert into the_table (col0, col1, col2) values
(1, 0, 0),
(2, 0, 0),
(3, 0, 0)

I cannot speak for MySQL, but SQL Server limits this to 1000 rows for each statement.

+3
source 
INSERT INTO the_table
    (col0, col1, col2)
    VALUES
    (1, 0, 0),
    (2, 0, 0),
    (3, 0, 0),
    ...
    (255, 0, 0)
+2

INSERT..SELECT:

INSERT INTO the_table (col0, col1, col2)
SELECT col0, col1, col2 FROM the_table_defaults
+2

More articles:


All Articles