Forum: Ruby on Rails Inserting data from 1 table to another by using find_by_sql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Salil G. (Guest)
on 2009-04-11 17:30
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'
Phlip (Guest)
on 2009-04-11 17:48
(Received via mailing list)
Salil G. 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...
Salil G. (Guest)
on 2009-04-11 18:13
> 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
Frederick C. (Guest)
on 2009-04-11 18:56
(Received via mailing list)
On Apr 11, 2:30 pm, Salil G. <removed_email_address@domain.invalid>
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
Salil G. (Guest)
on 2009-04-12 11:37
> 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
Frederick C. (Guest)
on 2009-04-12 11:58
(Received via mailing list)
On Apr 12, 8:37 am, Salil G. <removed_email_address@domain.invalid>
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
Salil G. (Guest)
on 2009-04-12 12:12
> 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
This topic is locked and can not be replied to.