Rails - Aug 2007 - 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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Aug 19, 7:28 pm, charlie caroff
<charlie.car...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Aug 20, 6:19 am,
"KathysK...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org"
<KathysK...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> On Aug 19, 7:28 pm,charlie
caroff<charlie.car...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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>
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---