Snow Giraffe Tech

In addition to some other super duper features in version 0.9.1 just released, ar-extensions plugin (and gem) now supports INSERT SELECT functionality for MySQL to move records from one or more tables into another. Instead of querying eleventy billion records, possibly running out of memory, and then so slowly inserting records one by one, ar-extensions extends ActiveRecord to do it all under the covers in one transaction without writing custom SQL.

# Insert one copy of each book into the shopping cart. 
  CartItem.insert_select(:from => :book,
                         :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now],
                         :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at]})

# GENERATED SQL example (MySQL):
# INSERT INTO `cart_items` ( `book_id`, `shopping_cart_id`, `copies`, `updated_at`, `created_at` )
# SELECT books.id, '134', 1, '2009-03-02 18:28:25', now() FROM `books`

Using the :keywords, :pre_sql, and :post_sql the  insert_select options, support all mysql options available to insert and select statements including

• IGNORE
• ON DUPLICATE KEY UPDATE
• LOW_PRIORITY
• SQL_CACHE
• DELAY

before and after comments and anything you can imagine you want to customize.

To install:

script/plugin install git://github.com/zdennis/ar-extensions.git

For more documentation, refer to  insert_select rdocs and main rdocs.

Filed under:
ActiveRecord, MySQL, Plugins, Ruby on Rails, ar-extensions by blythe