title: How to bulk insert data in a table using Rails and a single insert statement
body: <p>I have the following ActiveRecord models, Widget, Merchant, Store, StoresWidget (many-to-many association)</p>
  
  <pre><code>class Merchant &lt; ActiveRecord::Base
    has_many :stores
    has_many :widgets
  end
  
  class Widget &lt; ActiveRecord::Base
    has_many :stores_widgets
    has_many :stores, :through =&gt; :stores_widgets
    belongs_to :merchant
  end
  
  class Store &lt; ActiveRecord::Base
    has_many :stores_widgets
    has_many :widgets, :through =&gt; :stores_widgets
    belongs_to :merchant
  end
  
  class StoresWidget &lt; ActiveRecord::Base
    belongs_to :widget
    belongs_to :store
  end
  </code></pre>
  
  <p>So the corresponding tables are <code>widgets</code>, <code>merchants</code>, <code>stores</code> and <code>stores_widgets</code>, where <code>widgets</code> and <code>stores</code> each has a <code>id</code> column and <code>stores_widgets</code> has two columns <code>store_id</code> and <code>widget_id</code>. A Widget can be available in 1 or more stores and a store can have many widgets available. Some widgets are available in all stores some are only available in a sub-set of stores. If a Widget is restricted to a subset of stores, the column <code>restricted</code> is <code>true</code></p>
  
  <p>When a new store is added to a merchant I want to update all the unrestricted widgets to be associated with that store. Ideally I would like to have code like this in my <code>StoresController#create</code></p>
  
  <pre><code>class StoresController &lt; ApplicationController
    def create
      # build new store...
      Store.transaction do
        store.save!
        Widget.update_all_unrestricted_widgets_with_store(store)
      end
      render :show
    end
  end
  </code></pre>
  
  <p>Where <code>update_all_unrestricted_widgets_with_store</code> ends up executing SQL like:</p>
  
  <pre><code>INSERT INTO stores_widgets (store_id, widget_id) 
  (SELECT #{store.id}, widgets.id 
   FROM widgets
   WHERE widgets.merchant_id = #{store.merchant_id} 
   AND widgets.restricted = FALSE)
  </code></pre>
  
  <p>So if a merchant has 100000 widgets that are unrestricted a 100000 new stores_widgets rows are created in one INSERT rather than 100000 distinct INSERTS.</p>
  
  <p>Preferably I would like to get ActiveRecord to build an insert like this. Is that possible? If not would I be able to do this with ARel? I want to avoid executing a SQL string to achieve this if possible, so that I can maintain a level if separation between the code and the database SQL syntax.</p>