# How to insert multiple rows into a join, with one sql statem

**URL:** <https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482>\
**Category:** Rails\
**Created:** [August 20, 2007, 2:29am UTC](https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482 "2007-08-20T02:29:23Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![charlie\_caroff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a587f6/32.png) [@charlie\_caroff](https://www.ruby-forum.com/u/charlie_caroff)\
**Post date:** [August 20, 2007, 2:29am UTC](https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482/1 "2007-08-20T02:29:23Z")

</div>

Hi,

I’ve figured out how to import multiple rows into one table with one  
sql statement, using the syntax

Table.import columns, values

I make a list of column names, and I make a list of lists of data to  
import into each column.

It works great. Inserting 100 rows in one statement appears to be  
way, way faster than inserting one row at a time. But how do I do  
this using a join?

I have two tables, product\_lines and products. I want to import a lot  
of new products at once, hopefully using the above method.

product\_lines has a column called product\_id, and products has a  
column called id, which is a standard rails auto\_increment column. I  
guess I want to join the two tables, then import as described above.  
How do I do this?

Charlie

---

<div class="post-metadata">

**Author:** ![charlie\_caroff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a587f6/32.png) [@charlie\_caroff](https://www.ruby-forum.com/u/charlie_caroff)\
**Post date:** [August 20, 2007, 3:22pm UTC](https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482/2 "2007-08-20T15:22:11Z")

</div>

Charly,  
Would you mind sharing some of the code that allowed you to succeed in  
this ‘death defying feat’?  
I would be most grateful.  
Kathleen

---

<div class="post-metadata">

**Author:** ![charlie\_caroff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a587f6/32.png) [@charlie\_caroff](https://www.ruby-forum.com/u/charlie_caroff)\
**Post date:** [April 2, 2009, 4:44pm UTC](https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482/3 "2009-04-02T16:44:09Z")

</div>

can we used ar-extensions with rails 1.0.2  
actually it works fine with rails 2.0.2 and activerecord-2.3.2 and  
activesupport-2.3.2.  
But when i am using it with rails 1.0.2 it gives me following error

undefined method `expand\_hash\_conditions\_for\_aggregates’ for  
#Class:0x6d0d670

---

<div class="post-metadata">

**Author:** ![charlie\_caroff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a587f6/32.png) [@charlie\_caroff](https://www.ruby-forum.com/u/charlie_caroff)\
**Post date:** [August 20, 2007, 7:31pm UTC](https://www.ruby-forum.com/t/how-to-insert-multiple-rows-into-a-join-with-one-sql-statem/112482/4 "2007-08-20T19:31:54Z")

</div>

Be careful what you ask for – from a newbie, anyway!

For clarity’s sake - I’m using mysql.

My code is kludgy and complicated, and does more than just stick a  
bunch of rows into a database – so I’m going to try to boil it down  
so it makes sense to you. Hopefully this works.

Let’s say you have a table called “things”, represented by a model  
called “Thing”, with these columns:

id  
name  
street\_address  
updated\_at

You want to put three rows of data into things at the same time. You  
populate one array with symbols that correspond to the names of the  
fields in your database:

names = [:name, :street\_address]

(The id field is an auto\_increment field, and will be populated  
automatically. The updated\_at field is a special rails field, which  
will automatically be populated with the current timestamp.)

You populate another array with the data that you want to put in those  
fields. This array takes the form of an array of lists:

values = [[‘charlie’,‘123 elm street’],[‘kathy’,‘234 pine street’]]

then it’s just

Thing.import names, values

One thing that puzzles me is that I learned this syntax from here:

[http://www.igvita.com/blog/2007/07/11/efficient-updates-data-import-in-rails/](http://www.igvita.com/blog/2007/07/11/efficient-updates-data-import-in-rails/)

This site implies that you need the extension ar-extensions to make  
this syntax work. So I installed this ‘ar-extension’, then I  
commented out the ‘require ar-extensions’ line from my action – and  
the import syntax still works.

Charlie

On Aug 20, 6:19 am, “removed\_email\_address@domain.invalid” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)