Hi !
I would like to do a batch insertion. I would like the final SQL to
look something like this:
INSERT INTO models(name) VALUES (''abc''),
(''def''), (''ghi'');
Is there anything built-in in AR that allows me to do that ? Something
like:
new_models = Model.insert_batch([
{:name => ''abc''},
{:name => ''def''},
{:name => ''ghi''}
])
Thanks !
François
On 7/8/05, François Beausoleil <fbeausoleil-IQIa899fVSs@public.gmane.org> wrote:> I would like to do a batch insertion. I would like the final SQL to > look something like this: > INSERT INTO models(name) VALUES (''abc''), (''def''), (''ghi'');I''m almost 100% certain that there isn''t any SQL variant that would accept that kind of INSERT statement even if AR could do that. Inserts are a single row at a time. Just run a simple loop that does the inserts for you. You''ll more then likely have to loop anyways to get the data for each "row" you want to insert. -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
You can do that in mysql, but I don''t know if there''s support in AR. John Higgins wrote:>On 7/8/05, François Beausoleil <fbeausoleil-IQIa899fVSs@public.gmane.org> wrote: > > > >>I would like to do a batch insertion. I would like the final SQL to >>look something like this: >>INSERT INTO models(name) VALUES (''abc''), (''def''), (''ghi''); >> >> > >I''m almost 100% certain that there isn''t any SQL variant that would >accept that kind of INSERT statement even if AR could do that. Inserts >are a single row at a time. > >Just run a simple loop that does the inserts for you. You''ll more then >likely have to loop anyways to get the data for each "row" you want to >insert. > > >
* John Higgins (wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org) wrote:> On 7/8/05, François Beausoleil <fbeausoleil-IQIa899fVSs@public.gmane.org> wrote: > > > I would like to do a batch insertion. I would like the final SQL to > > look something like this: > > INSERT INTO models(name) VALUES (''abc''), (''def''), (''ghi''); > > I''m almost 100% certain that there isn''t any SQL variant that would > accept that kind of INSERT statement even if AR could do that. Inserts > are a single row at a time.These are MySQL Extended Inserts, as used by mysqldump. Nice, but I''d just bypass AR and build the insert myself for the few places I would use it. -- Thomas ''Freaky'' Hurst http://hur.st/
On Jul 8, 2005, at 11:13 AM, François Beausoleil wrote:> > I would like to do a batch insertion. I would like the final SQL > to look something like this: > INSERT INTO models(name) VALUES (''abc''), (''def''), (''ghi''); > > Is there anything built-in in AR that allows me to do that ? > Something like:If you''re looking to avoid the performance overhead of a separate commit on each insert, you can do it by wrapping your creates/saves/ updates in a transaction block, as follows: transaction do Model.new({:name => ''abc''}).save Model.new({:name => ''def''}).save Model.new({:name => ''ghi''}).save end In terms of insert style, it looks like this''ll work also, though I haven''t had occasion to use this approach yet: transaction do Model.create( [ {:name => ''jkl''}, {:name => ''mno''}, {:name => ''pqrs''} ] ) end The transaction wrapper will suppress the individual transactions normally surrounding saves, and commit will succeed or fail on the actions of the block as a whole. - Lee _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails