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.
C42c706fab23da0b6d3cd2cfbb8db27d?d=identicon&s=25 Salil Gaikwad (salil)
on 2009-04-11 15: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'
Aafa8848c4b764f080b1b31a51eab73d?d=identicon&s=25 Phlip (Guest)
on 2009-04-11 15:48
(Received via mailing list)
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...
C42c706fab23da0b6d3cd2cfbb8db27d?d=identicon&s=25 Salil Gaikwad (salil)
on 2009-04-11 16: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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-04-11 16:56
(Received via mailing list)
On Apr 11, 2:30 pm, Salil Gaikwad <rails-mailing-l...@andreas-s.net>
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
C42c706fab23da0b6d3cd2cfbb8db27d?d=identicon&s=25 Salil Gaikwad (salil)
on 2009-04-12 09: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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-04-12 09:58
(Received via mailing list)
On Apr 12, 8:37 am, Salil Gaikwad <rails-mailing-l...@andreas-s.net>
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
C42c706fab23da0b6d3cd2cfbb8db27d?d=identicon&s=25 Salil Gaikwad (salil)
on 2009-04-12 10: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.