CODEWITHSUNDEEP
ruby-on-railsactiverecord
Tommy
Tommy

Reputation: 1819

Merging two tables the rails way

I'm using Rails 2.3.5 with ActiveRecord, ruby 1.8.6, mysql (2.8.1)

I have two tables A and B with the exact same column structures. A is on a separate server from B.

I want to take the entire contents of B and add them to A so

A = A + B

Any elegant methods? I thought about taking one record at a time from B and just adding it into A, but that doesn't seem that great..

Upvotes: 0

Views: 3543

Answers (3)

noodl
noodl

Reputation: 17408

To permanently copy the contents of one table to another you'll need a variation of the INSERT query syntax. You don't specify which RDBMS you're using so I'll just go ahead and show how it'd work with MySQL.

INSERT A SELECT * FROM B

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

EDIT

If your tables are hosted on separate servers you'll need to copy one of them so they're on the same MySQL instance. That can be done with the mysqldump and mysql commands, like:

mysqldump -h <hostname> <database_name> <table_name> |mysql -h <other_hostname> <other_database_name>

or, if you prefer to use ssh

ssh <hostname> mysqldump <database_name> <table_name> |ssh <other_hostname> mysql <other_database_name>

Upvotes: 1

coreyward
coreyward

Reputation: 80041

You can do this two places, your SQL, or in your application.

Through a SQL query:

SELECT * FROM table_a
UNION
SELECT * FROM table_b;

In your Rails application:

a = A.all
b = B.all
ab = a + b

Simple enough.

UPDATE: Since you want to make this change permanently, you should create a migration that will merge the two tables together. This will make sure Rails knows what's going on, and it will allow you to migrate your production database similarly. Because ActiveRecord doesn't have a built-in table merge, you would execute a raw SQL query in your migration like so:

class MergeTableAWithTableB < ActiveRecord::Migration
  def self.up
    execute "INSERT INTO `table_a` (field_1, field_2, field_3) SELECT (field_1, field_2, field_3) FROM `table_b`;"
    drop_table :table_b
  end

  def self.down
    raise IrreversibleMigration
  end
end

Then run rake db:migrate at the command line to have this action performed.

This has the effect of re-indexing the rows from table_b, though, since moving them over to table_a would result in conflicting primary ids.

Upvotes: 2

Phrogz
Phrogz

Reputation: 303253

It looks like ActiveRecord (unlike Sequel) does not support batch insert by default. Look at this Stack Overflow answer for a library that extends ActiveRecord so that you can do a single efficient update rather than one SQL insert for each row.

Upvotes: 0

Related Questions