23

I need to insert a array of emails as different records into my contacts table. How can this be done.

Eg: @email = ["a@b.com", "c@d.com", "e@f.com", ... ]

I dont want to use.

  @email.each do |email|
     @contact = Contact.new
     @contact.email = email
     @contact.save
  end

This cause n insert quires. I just need a single insert query to insert these values. How can this be done in rails 3.0.9 (and ideally MySQL). Please help

Craig Walker
  • 49,871
  • 54
  • 152
  • 212
Amal Kumar S
  • 15,555
  • 19
  • 56
  • 88
  • See also: [Bulk Insert records into Active Record table](http://stackoverflow.com/questions/15317837/bulk-insert-records-into-active-record-table) and [Batch insertion in rails 3](http://stackoverflow.com/questions/15784305/batch-insertion-in-rails-3). –  Apr 13 '14 at 05:07

4 Answers4

51

activerecord-import implements AR#import

activerecord-import is a library for bulk inserting data using ActiveRecord.

see how it works:

books = []
10.times do |i| 
  books << Book.new(:name => "book #{i}")
end
Book.import books

Project's home is on Github and it's wiki.

Anatoly
  • 15,298
  • 5
  • 53
  • 77
  • 1
    Speeds up a bulk insert significantly. – gak Jun 03 '12 at 23:57
  • Also, related question: http://stackoverflow.com/questions/13718013/how-do-i-retrieve-a-list-of-created-ids-for-bulk-insert-in-active-record, see the second answer (not the accepted one). The raw SQL version does return all the ids. – Rob Jul 03 '13 at 15:15
  • And I've got a fork of the gem that not only does that, but automagically efficiently saves any subobjects and their children as well, so that you don't have to maintain parallel sets of arrays to save more complex objects. See here: https://github.com/GoodMeasuresLLC/activerecord-import – Rob Sep 06 '13 at 09:47
  • 3
    I successfully used this gem to reduce a 260k row data migration from 1 hour down to 6 minutes. Using Postgres. – mpoisot Sep 09 '13 at 19:47
  • This is fine. Thanks for the answer.. But, I would like to have this feature for a Model-less table in rails. Is that Possible..?? I am stuck at Model.import. I do not have Model here. Also, I can not create an empty model for this table. That is the situation. Please dont ask me why.. – Breen ho Jul 24 '15 at 13:17
  • @Breenho In that case why not to prepare INSERT in batches by appending new strings and execute SQL request at result? – Anatoly Jul 24 '15 at 13:49
  • Thanks for your response. @Anatoly, Yeah., That was the last option I had. Without using direct SQL queries, Is there a way to do so..? – Breen ho Jul 24 '15 at 14:50
  • @Breenho it depends on how do you create objects with Model-less table now – Anatoly Jul 24 '15 at 15:57
  • Hey @Anatoly., this is the link of my question . http://stackoverflow.com/questions/31612059/is-that-possible-to-make-use-of-activerecord-import-gem-for-a-model-less-table. Please, check this out for more details including the code snippet. – Breen ho Jul 24 '15 at 16:15
  • @Breenho I replied to you, please check it out – Anatoly Jul 26 '15 at 16:55
  • This is an old answer, but this is exactly what i want. But when I Implemented, getting this error "Column 'created_at' cannot be null". This library is not setting default value for created_at. Any idea why? – Rajesh Jun 12 '17 at 17:34
  • @Rajesh sure, check [this out](https://github.com/zdennis/activerecord-import/issues/426#issuecomment-307689597) – Anatoly Jun 12 '17 at 17:43
  • hmm, so I have to wait till they release an update, till then I'll use a plain bulk update query – Rajesh Jun 12 '17 at 18:01
  • @Rajesh v0.19.0 – Anatoly Jun 25 '17 at 22:01
4

You might also try upsert, which is approximately as fast as activerecord-import, but only works (currently) with MySQL, Postgres, and SQLite3:

require 'upsert'
Upsert.batch(Contact.connection, Contact.table_name) do |upsert|
  emails.each do |email|
    upsert.row(email: email)
  end
end

Note that this involves one database query per record, but it's an "upsert," so you don't have to check if a record already exists. In your example, this isn't a concern, but in most applications it becomes one eventually.

Seamus Abshere
  • 8,326
  • 4
  • 44
  • 61
  • Having one database query per record is exactly NOT bulk import. Bulk import is having one insert SQL statement with many parameter sets bound to it, so that you have only one database round trip, and hence it's fast. – Rob Jul 03 '13 at 13:47
  • `upsert` library started out using `INSERT ON DUPLICATE KEY UPDATE`, which is bulk. – Seamus Abshere Jul 05 '13 at 17:38
  • (oops, hit enter before i could fully explain myself) even though it doesn't do that anymore, it makes 1 trip to the db per record, i think that historical fact is why i unthinkingly didn't justify how `upsert` is, in my opinion, a form of bulk update. i would define bulk update as "setting new state across many records without regard to their current state" - how many db trips that takes is an (important) detail. "upserting" is a particular way of doing bulk. see https://github.com/seamusabshere/upsert/commit/b8365d91fe68e450382119 for why i stopped using the 1-trip-per-batch method. – Seamus Abshere Jul 05 '13 at 17:44
3

The simplest way without additional gem is to concat a string and execute it in one SQL insertion (http://www.electrictoolbox.com/mysql-insert-multiple-records/).

@email = ["a@b.com", "c@d.com", "e@f.com"]

time = Time.current.to_s(:db)

values = @email.map do |email|
  "('#{email}', '#{time}', '#{time}')"
end

sql = "INSERT INTO contacts (email, created_at, updated_at) VALUES #{values.join(', ')}"
Contact.connection.execute(sql)
nnattawat
  • 686
  • 5
  • 11
  • I implemented this, but now I'm wondering if it's at risk of SQL injection if you are using something a bit more free form than an email. – Nuclearman Apr 13 '16 at 16:44
1

I just wrote a little monkey-patch for Active Record 3.2 to INSERT many new records with a single SQL query, check it out:

https://github.com/alexdowad/showcase/blob/master/activerecord/bulk_db_operations.rb

Alex D
  • 29,755
  • 7
  • 80
  • 126