Inserting data from 1 table to another by using find_by_sql

Rails
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:inselect_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:incache_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:infind_by_sql’
app/controllers/artists_controller.rb:8:in `index’

2

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…

3

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

4

On Apr 11, 2:30 pm, Salil G. [email protected]
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

5

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

6

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

7

On Apr 12, 8:37 am, Salil G. [email protected]
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