CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordarel
Amal Kumar S
Amal Kumar S

Reputation: 16075

How to implement bulk insert in Rails 3

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

Eg: @email = ["[email protected]", "[email protected]", "[email protected]", ... ]

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

Upvotes: 23

Views: 17234

Answers (4)

nnattawat
nnattawat

Reputation: 716

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 = ["[email protected]", "[email protected]", "[email protected]"]

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)

Upvotes: 4

Alex D
Alex D

Reputation: 30485

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

Upvotes: 1

Seamus Abshere
Seamus Abshere

Reputation: 8526

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.

Upvotes: 4

Anatoly
Anatoly

Reputation: 15530

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.

Upvotes: 51

Related Questions