INSERT multiple records using ruby on rails active record

Question

Is there a way to INSERT multiple records rather than one at a time?

I have a very very ugly rake task that is doing the following ...

        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2000-03-07", :party => row[45], :participate => participated(row[45]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2000-11-07", :party => row[46], :participate => participated(row[46])) 
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2000-05-08", :party => row[47], :participate => participated(row[47]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2001-11-06", :party => row[48], :participate => participated(row[48]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2002-05-07", :party => row[49], :participate => participated(row[49]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2002-11-05", :party => row[50], :participate => participated(row[50]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2003-05-06", :party => row[51], :participate => participated(row[51]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2003-11-04", :party => row[52], :participate => participated(row[52]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2004-03-02", :party => row[53], :participate => participated(row[53]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2004-11-02", :party => row[54], :participate => participated(row[54]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2005-02-08", :party => row[55], :participate => participated(row[55]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2005-05-03", :party => row[56], :participate => participated(row[56]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2005-09-13", :party => row[57], :participate => participated(row[56]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2005-11-08", :party => row[58], :participate => participated(row[58]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2006-02-07", :party => row[59], :participate => participated(row[59]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2006-05-02", :party => row[60], :participate => participated(row[60]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2006-11-07", :party => row[61], :participate => participated(row[61]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-05-08", :party => row[62], :participate => participated(row[62]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-09-11", :party => row[63], :participate => participated(row[63]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2007-11-06", :party => row[64], :participate => participated(row[64]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-11-06", :party => row[65], :participate => participated(row[65]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-12-11", :party => row[66], :participate => participated(row[66]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2008-03-04", :party => row[67], :participate => participated(row[67]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2008-10-14", :party => row[68], :participate => participated(row[68]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2008-11-04", :party => row[69], :participate => participated(row[69]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2008-11-18", :party => row[70], :participate => participated(row[70]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-05-05", :party => row[71], :participate => participated(row[71]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-08", :party => row[72], :participate => participated(row[72]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-15", :party => row[73], :participate => participated(row[73]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-29", :party => row[74], :participate => participated(row[74]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2009-11-03", :party => row[75], :participate => participated(row[75]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-05-04", :party => row[76], :participate => participated(row[76]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-07-13", :party => row[77], :participate => participated(row[77]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-09-07", :party => row[78], :participate => participated(row[78]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2010-11-02", :party => row[79], :participate => participated(row[79]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-05-03", :party => row[80], :participate => participated(row[80]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81]))
        VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]))

This has to be extremely inefficient and there must be a better way...

"This has to be extremely inefficient" - what makes you think so? — Sergio Tulentsev
– Sergio Tulentsev, Commented Apr 5, 2012 at 3:11
its ugly. haha. If there is a better way I could benchmark the two and get back with you? — JZ.
– JZ., Commented Apr 5, 2012 at 3:17

Cody Poll · Accepted Answer · 2016-04-15 21:38:31Z

71

The create method takes also an array as parameter.

VoteRecord.create(
  [
    { :prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]) },
    { :prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81]) }
    ...
  ]
)

However, this still executes one SQL query per entry instead of a single SQL query. It is more efficent, because it only has to create a single activerecord object under the hood.

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.3, “Server System Variables”.

From the mysql page (but I guess it should be the same for other dbs)

edited Apr 15, 2016 at 21:38

Cody Poll

8,3204 gold badges30 silver badges22 bronze badges

answered Apr 5, 2012 at 3:19

klump

3,2692 gold badges26 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Bachir Over a year ago

this does not do a single SQL statement, it does one per new object. i'm trying this on rails 3.2 -- maybe it was different in previous versions?

Nolan Amy Over a year ago

@JohnBachir Not sure about the various versions, but if you want to ensure that it's done at once (for performance), you can use a transaction. Though maybe rails is already doing that?..

John Bachir Over a year ago

@Nolan a transaction will not make it done in one statement. that's what i'm referring to -- doing all the inserts with one sql statement.

John Bachir Over a year ago

@Nolan alas, it's not possible via ActiveRecord (unless it's been added in rails 4)

kamal Over a year ago

What about PL/SQL procedures? Using it the network latency will reduced to 1, Correct me if i am wrong @Cody Poll

|

nonrectangular · Accepted Answer · 2013-07-13 20:52:37Z

22

Unfortunately, it's not possible in Rails out of the box.

However, activerecord-import is a great gem for Rails 3.x that adds an import method to your model classes, and does exactly what you want as a single SQL insert statement.

answered Jul 13, 2013 at 20:52

nonrectangular

2,15920 silver badges9 bronze badges

2 Comments

Ameet Wadhwani Over a year ago

works beautifully with my Rails 4 Postgres 9.3 Ruby 2.1 setup and is infinitely faster than my previous method of calling create on each iteration or Klump's method of calling create once on an array.

allenwlee Over a year ago

this article was very helpful to me coffeepowered.net/2009/01/23/…

sideshowbarker · Accepted Answer · 2023-05-20 00:09:17Z

15

Rails 6 now supports this out of the box:

https://web.archive.org/web/20191216050322/https://blog.bigbinary.com/2019/04/15/bulk-insert-support-in-rails-6.html

Bulk inserts can be performed using newly added methods: insert_all, insert_all! and upsert_all.

edited May 20, 2023 at 0:09

sideshowbarker

89.3k30 gold badges220 silver badges216 bronze badges

answered Sep 25, 2019 at 18:34

Luke

2,0631 gold badge18 silver badges25 bronze badges

Comments

user17952967 · Accepted Answer · 2022-06-15 21:07:22Z

4

Since Ruby on Rails 6.0 the method insert_all is available and saves multiple records into the database with just a single query, good for constant time complexity O(1)

VoteRecord.insert_all([
    { :prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]) },
    { :prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81]) }
    ...
  ])

answered Jun 15, 2022 at 21:07

user17952967

Comments

Ari · Accepted Answer · 2015-07-09 00:06:53Z

-9

You can wrap your ActiveRecord statements inside an ActiveRecord::Base.transaction:

ActiveRecord::Base.transaction do 
  1000.times { Post.create(options) } 
end

See this post for other techniques.

answered Jul 9, 2015 at 0:06

Ari

2,00421 silver badges32 bronze badges

2 Comments

Adamantish Over a year ago

The OP asked whether they could do better than inserting one at a time. A transaction doesn't prevent that though it helps a bit. This benchmark for instance finds that whilst putting in a transaction will improve by 30% a single INSERT was 7000% faster. I'd go with what @nonrectangular said.

Antony Over a year ago

Please don't use this as this will not commit to database until all 1000 records are inserted. This is like postponing the problems.

Collectives™ on Stack Overflow

INSERT multiple records using ruby on rails active record

5 Answers 5

6 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related