how to insert multiple rows into a join, with one sql statement?

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

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

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/

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

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>