Batch insertion in rails 3

Question

I want to do a batch insert of few thousand records into the database (POSTGRES in my case) from within my Rails App.

What would be the "Rails way" of doing it? Something which is fast and also correct way of doing it.

I know I can create the SQL query by string concatenation of the attributes but I want a better approach.

Answer 1

ActiveRecord .create method supports bulk creation. The method emulates the feature if the DB doesn't support it and uses the underlying DB engine if the feature is supported.

Just pass an array of options.

# Create an Array of new objects
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])

Block is supported and it's the common way for shared attributes.

# Creating an Array of new objects using a block, where the block is executed for each object:
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }]) do |u|
  u.is_admin = false
end

Answer 2

I finally reached a solution after the two answers of @Simone Carletti and @Sumit Munot.

Until the postgres driver supports the ActiveRecord .create method's bulk insertion, I would like to go with activerecord-import gem. It does bulk insert and that too in a single insert statement.

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

In POSTGRES it lead to a single insert statemnt.

Once the postgres driver supports the ActiveRecord .create method's bulk insertion in a single insert statement, then @Simone Carletti 's solution makes more sense :)

Answer 3

You can create a script in your rails model, write your queries to insert in that script In rails you can run the script using

rails runner MyModelName.my_method_name

Is the best way that i used in my project.

Update:

I use following in my project but it is not proper for sql injection. if you are not using user input in this query it may work for you

user_string = " ('[email protected]','a'), ('[email protected]','b')"
User.connection.insert("INSERT INTO users (email, name) VALUES"+user_string)

For Multiple records:

new_records = [
  {:column => 'value', :column2 => 'value'}, 
  {:column => 'value', :column2 => 'value'}
]

MyModel.create(new_records)