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, Apr 05 '12 at 03:11
its ugly. haha. If there is a better way I could benchmark the two and get back with you? — JZ., Apr 05 '12 at 03:17

score 70 · Accepted Answer · edited Apr 15 '16 at 21:38

70

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 '16 at 21:38

Cody Poll

7,690
3
27
22

answered Apr 05 '12 at 03:19

klump

3,259
2
23
26

25

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? – John Bachir Jan 29 '13 at 00:34
@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?.. – Nolan Amy Mar 10 '13 at 06:32
2

@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 Mar 12 '13 at 04:49
@JohnBachir Ah. Well let me know if you do figure it out - I could use it as well. – Nolan Amy Mar 12 '13 at 05:11
3

@Nolan alas, it's not possible via `ActiveRecord` (unless it's been added in rails 4) – John Bachir Mar 12 '13 at 17:47
1

What about PL/SQL procedures? Using it the network latency will reduced to 1, Correct me if i am wrong @Cody Poll – kamal Aug 16 '16 at 06:57

score 21 · Answer 2 · answered Jul 13 '13 at 20:52

21

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 '13 at 20:52

nonrectangular

2,080
20
9

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. – Ameet Wadhwani Jul 01 '14 at 02:56
1

this article was very helpful to me https://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/ – allenwlee Feb 07 '16 at 17:07

score 15 · Answer 3 · edited May 20 '23 at 00:09

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 '23 at 00:09

sideshowbarker

81,827
26
193
197

answered Sep 25 '19 at 18:34

Luke

2,053
1
18
25

score 2 · Answer 4 · answered Jun 15 '22 at 21:07

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

score -9 · Answer 5 · answered Jul 09 '15 at 00:06

-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 09 '15 at 00:06

Ari

1,974
19
30

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](https://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/) 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. – Adamantish Jul 04 '16 at 18:47
Please don't use this as this will not commit to database until all 1000 records are inserted. This is like postponing the problems. – Antony May 11 '17 at 13:41

INSERT multiple records using ruby on rails active record

5 Answers5

Linked