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
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
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.
upsert library started out using INSERT ON DUPLICATE KEY UPDATE, which is bulk.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 github.com/seamusabshere/upsert/commit/b8365d91fe68e450382119 for why i stopped using the 1-trip-per-batch method.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)
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
