CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecordinsert
JZ.
JZ.

Reputation: 21907

INSERT multiple records using ruby on rails active record

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...

Upvotes: 48

Views: 45879

Answers (5)

Luke
Luke

Reputation: 2063

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.

Upvotes: 15

Luis
Luis

Reputation: 41

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]) }
    ...
  ])

Upvotes: 3

klump
klump

Reputation: 3269

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)

Upvotes: 71

Ari
Ari

Reputation: 2004

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.

Upvotes: -9

nonrectangular
nonrectangular

Reputation: 2119

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.

Upvotes: 22

Related Questions