[Resolved] Can I...multiple inserts with 1 INSERT

s_shrum

Is there a way to do multiple inserts into a MySQL database like:

Insert into table (record1values), (record2values), (etc), (etc);

...vs...

Insert into table (record1values);
Insert into table (record2values);
Insert into table (etc);
Insert into table (etc);

I haven't tried it yet but figured I check first. I looked at the MySQL docs but didn't see anything that addressed this directly

Can it be done? Is there a limit the the string length of the SQL statment?

TIA

Sean Shrum
http://www.shrum.net

barand

the query you want is

INSERT INTO table1 (a, b, c ) SELECT x,y,z FROM table2

hth

BlueMeteor

write your INSERT like this....

Insert into table (record1_name) values (record1_values);

BlueMeteor

Originally posted by barand
the query you want is

INSERT INTO table1 (a, b, c ) SELECT x,y,z FROM table2

hth

His wants was multiple inserts instead of one

s_shrum

True...I want multiple inserts...the data is coming from files...not another database

I've a number of files that I'm collecting data from.

I wanted to know if I could dump all the data from all the various files that I collect (in an array) into a MySQL database with 1 INSERT call versus having to do a INSERT for every file I have:

One-in-one (what I'm trying to avoid):

insert into table set (name=$array[1][name], size=$array[1][size]);
insert into table set (name=$array[2][name], size=$array[2][size]);
insert into table set (name=$array[3][name], size=$array[3][size]);

Many-in-one (what I would rather use):

insert into table set (name=$array[1][name], size=$array[1][size]), (name=$array[2][name], size=$array[2][size]), (name=$array[3][name], size=$array[3][size]), etc...

Seems like I should be able to do this although I still haven't tried it.

Moonglobe

insert into table(list_of_cells) values (value_list1), (value_list2), etc....

hth
moon

ybinds

Try using Mysql statement "LOAD DATA LOCAL INFILE" and see.

If you are interested I can give you the syntax and some description about this command. Basically it tries to load the data from text files to Mysql database.

barand

Originally posted by BlueMeteor
His wants was multiple inserts instead of one

Exactly. If there are 50 records in table2 then the fields from all 50 will be inserted into 50 new records in table1 by my query.

Don't knock it till you've tried it 🙂

s_shrum

Moonglobe++

Moonglobe hit it on the nose. That's what I was looking for.

Thanx