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 开发者_StackOverflow社区that great..

---

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.

---

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>

---

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.