Inserting data from 1 table to another by using find_by_sql

rubyonrails-talk
1

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'

2

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

3

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

4

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

5

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