Rails - Apr 2009 - Inserting data from 1 table to another by using find_by_sql

Hi All,
 I am inserting data from one table to another by using following method

@data=RoyaltyReportFiles.find_by_sql("insert into royalty_reports
(artist_name, album_name) select artist_name, album_name from
temp_royalty_reports where id=328417")


it saves the data into royalty_reports table but it gives following
error and application crashes. i am using rails 1.2.5

You have a nil object when you didn''t expect it!
The error occurred while evaluating nil.all_hashes

vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:482:in
`select''
vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all_without_query_cache''
vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:53:in
`select_all''
vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:74:in
`cache_sql''
vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:53:in
`select_all''
vendor/rails/activerecord/lib/active_record/base.rb:533:in
`find_by_sql''
app/controllers/artists_controller.rb:8:in `index''
-- 
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
-~----------~----~----~----~------~----~------~--~---

Salil Gaikwad wrote:
>  I am inserting data from one table to another by using following method
> 
> @data=RoyaltyReportFiles.find_by_sql("insert into royalty_reports
> (artist_name, album_name) select artist_name, album_name from
> temp_royalty_reports where id=328417")
Use either count_by_sql(), execute(), or the method higher than execute() whose 
name I forget...

Also, AR can insert one record for you very easily...


--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

> Use either count_by_sql(), execute(), or the method higher than 
> execute() whose
> name I forget...
> 
> Also, AR can insert one record for you very easily...
Actually i''m trying to insert multiple data from one table to another 
table but i take only one data here for example.
i try ar-extensions but as i''m using rails 1.2.0 it gives me following 
error

undefined method `expand_hash_conditions_for_aggregates'' for 
#<Class:0x4593c48>
which is related to the ar-extensions , activerecord gem

 i also like to know how to use the count_by_sql(), execute() method


Regards,

Salil
-- 
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
-~----------~----~----~----~------~----~------~--~---

On Apr 11, 2:30 pm, Salil Gaikwad
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Hi All,
>  I am inserting data from one table to another by using following method
>
> @data=RoyaltyReportFiles.find_by_sql("insert into royalty_reports
> (artist_name, album_name) select artist_name, album_name from
> temp_royalty_reports where id=328417")
That''s not a good idea. When you use find_by_sql, rails will try and
create a result set array (because it assumes you are doing a select).
RoyaltyReportFiles.connection.execute allows you to execute arbitrary
sql, however if you are doing an insert you should use
RoyaltyReportFiles.connection.insert ( the only difference is
readability and the fact that insert will flush Rails'' query cache
whereas execute will not).


Fred
>
> it saves the data into royalty_reports table but it gives following
> error and application crashes. i am using rails 1.2.5
>
> You have a nil object when you didn''t expect it!
> The error occurred while evaluating nil.all_hashes
>
> vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapt
er.rb:482:in
> `select''
> vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/da
tabase_statements.rb:7:in
> `select_all_without_query_cache''
> vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/qu
ery_cache.rb:53:in
> `select_all''
> vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/qu
ery_cache.rb:74:in
> `cache_sql''
> vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/qu
ery_cache.rb:53:in
> `select_all''
> vendor/rails/activerecord/lib/active_record/base.rb:533:in
`find_by_sql''
> app/controllers/artists_controller.rb:8:in `index''
> --
> Posted viahttp://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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> That''s not a good idea. When you use find_by_sql, rails will try
and
> create a result set array (because it assumes you are doing a select).
> RoyaltyReportFiles.connection.execute allows you to execute arbitrary
> sql, however if you are doing an insert you should use
> RoyaltyReportFiles.connection.insert ( the only difference is
> readability and the fact that insert will flush Rails'' query cache
> whereas execute will not).
> 
> 
> Fred
Hi Fred,
 thanks a million. it works...
I write a following code and it works like a magic........


RoyaltyReportFiles.connection.insert("insert into royalty_reports 
(artist_name, album_name, product_type, currency, status, upc, isrc, 
track_title, revenue, units, file_id, local_revenue) select artist_name, 
album_name, product_type, currency, status, upc, isrc, track_title, 
revenue, units, file_id, local_revenue
from temp_royalty_reports where id>=336401")

it saves 50 data from temp_royalty_reports to royalty_reports.

Fred i want to know one more thing is it good practise for inserting 
thousands of data from one table to another?
waiting for your reply...


Salil

-- 
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
-~----------~----~----~----~------~----~------~--~---

On Apr 12, 8:37 am, Salil Gaikwad
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> Fred i want to know one more thing is it good practise for inserting
> thousands of data from one table to another?
> waiting for your reply...
>
Well you wouldn''t want to be doing that on every single request, but
if you do need to move that amount of data around, why not.

Fred
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> Well you wouldn''t want to be doing that on every single request,
but
> if you do need to move that amount of data around, why not.
> 
> Fred
Hi Fred,
 Ok. Thanks again for your quick reply....

Regards,

Salil
-- 
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
-~----------~----~----~----~------~----~------~--~---