Is there a way to transfer info from one database into another database?

Question

I was wondering today if it was possible to transfer data from one database to another with one query. Say I have two tables:

CREATE TABLE `Table_One` (
  `ID` int(11) NOT NULL auto_increment,
  `Type_ID` int(11) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Date` varchar(100) NOT NULL,
  `Address` varchar(100) NOT NULL,
  `Town` varchar(100) NOT NULL,
  `Desc` longtext NOT NULL,
  `Inserted` varchar(100) NOT NULL,
  `Updated` varchar(100) NOT NULL,
  `User_ID` int(11) NOT NULL,
  `Pending` varchar(255) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;

and

CREATE TABLE `Table_Two` (
  `ID` int(11) NOT NULL auto_increment,
  `Title` varchar(255) NOT NULL,
  `Town` varchar(255) NOT NULL,
  `Desc` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I was thinking of doing something along the lines of

INSERT INTO Table_Two (0,SELECT Title, Town, Desc FROM Table_One)

This didn't seem right though because how would Table_Two know Table_One was in another database? Can I use the schema file to make it a more specific query? Is this even possible to do without using a server side language?

Thanks,
Levi

Answer 1

Actually you can:

SELECT ID, TITLE (all the params)
INTO Table_Two
FROM Table_One

as long as the tables are identical it should go pretty smoothly.

Hope this helps

Answer 2

insert into schema1.table_two (title,town,desc)
select title,town,desc
from schema2.table_one;

Are you talking about different instances?

Answer 3

Yes, there is a way. Prefix the table names with their respective databases in your queries, e.g. (if you are running the query from Table_Two's database:

INSERT INTO Table_Two (Field1, Field2, Field3) 
SELECT t.Field1, t.Field2, t.Field3 FROM anotherdb.Table_One t;

From MySQL documentation

You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly. You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference unless the reference would be ambiguous. See Section 8.2.1, “Identifier Qualifiers”, for examples of ambiguity that require the more explicit column reference forms.

Answer 4

Depending on the size of the source table, this may not be the most efficient way to move data from one database (instance? schema?) to another. I see you have the question tagged, "mysql," so I'm going to assume that's the product you are using. If you're moving a big table from one schema to another (whether they're in separate instances, or not), it may be quicker to use mysqldump to export out of one, and then import into the other.