Insert multiple rows with a single query

[jakeasmith]

Is there a way to build a query that inserts multiple value sets?

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

If not, do you think it makes sense to add that functionality?

[harikt]

This is a nice functionality to add. Currently we don't have this support.

[harikt]

I also love to have multiquery execution. But we already discussed this in Aura.Sql auraphp/Aura.Sql#22

[pmjones]

I can see where this would be useful, but I'm afraid I have other things that have to take priority. However, if you want to create a new MultipleInsert (or InsertMany or InsertMulti) class that exhibits this behavior and send a PR for it, I'd be happy to review.

[arcturial]

I am working on a prototype for doing bulk inserts.

https://github.com/arcturial/Aura.SqlQuery/tree/feature-bulk-insert

Once the idea is solid I will do some cleanup before doing a PR. Have a look when you have a moment to see if this is what you had in mind.

The way you would use it is as follows:

$query->newBulkInsert()
           ->cols(array('col1', 'col2', 'col3'))
           ->bindValues(array(
                    array(
                            'col1' => 'row1a',
                            'col2' => 'row1b',
                            'col3' => 'row1c',
                    ),
                    array(
                            'col1' => 'row2a',
                            'col2' => 'row2b',
                            'col3' => 'row2c'
                    )
             ));

[harikt]

@arcturial it looks good to me. Appreciate your good work on it.

[arcturial]

I moved the code to a new branch and changed it so that it now uses the same interface as the Insert() queries.

https://github.com/arcturial/Aura.SqlQuery/tree/feature-bulk-insert

The behaviour of some of the methods had to change slightly (like cols() not supporting associative arrays) since we are dealing with bulk data and not single values, but I don't think this should cause usability problems. I am not really sure why someone would want to use cols() with an associative array when doing bulk inserts anyway.

[arcturial]

I updated the README in that branch with more detailed usage instructions.

[harikt]

@arcturial send a PR so it will be easy to get reviewed.

Thanks!

[arcturial]

#56, request created

[pmjones]

This has been added as of c9dc2af

Many thanks to @arcturial for providing the impetus behind it.