SQL query to move data from one database to another 1

[CharlieSW10]

I am a newcomer to MySQL and am trying to work out how I can automate moving data from a table within one MySQL database to a table in another.

I want to ensure that changes made to a table in Database A are reflected in Database B. I assume that the best way to do this would be to use the views and triggers in MySQL 5. Unfortunately both databases are part of packages that use MySQL Server 4.1 and I cannot upgrade at the moment so this option is not open to me.

The update will only ever be in one direction so I think the best solution may be to define a SQL statement that selects all records in one TableX/DatabaseA that have been recently changed and writes the amended records to TableZ/databaseB.

I think this will require an INSERT statement but cannot find a syntax that allows me to enquire on a table in Database A and insert the results into Database B.

Could anyone please help with the syntax I need or perhaps suggest a better approach.

Thank you for your help.

Charlie

 

[KarveR]

check out mysql.com for replication . does what it says on the tin


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[CharlieSW10]

Thanks for your help, I did look at replication but from what I could make out it would not allow me to selectively pull out some columns from one table and write them into another table (in another database)which has a different structure.

The fields I want to move from Database A to Database B are common to both but each table is different.

Can Replication help with this scenario ?

 

[KarveR]

Ah, I see. Are the databases on the same physical server ?


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[CharlieSW10]

Yes

 

[CharlieSW10]

I forgot to mention that I'm hoping I can find an SQL statement that will do the job and then run it as a cron job every 10 minutes.

What I'm actually doing is ensuring that product details held within an ECommerce shopping cart are kept in sync with the warehouse management system where new products are created and maintained.

 

[KarveR]

this may be of some assistance then.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[CharlieSW10]

Thank you for the steer and the quick response, I'll try in now.

 

[CharlieSW10]

Works a treat - the relevant section is in the comments to the MySQL documentation:

Using INSERT INTO ... SELECT you can copy data from one database to another. You need the correct privileges for both.

Enter the source database, database1:
use database1;

Then write to which fields in the destination database you want to copy to, database2:
INSERT INTO database2.table1 (field1,field3,field9)
SELECT table2.field3,table2.field1,table2.field4
FROM table2;

The order of the selected fields and the inserted fields must match, so you enter the correct data. Before doing this, use "describe database2.table1" and "describe database1.table2", to make sure the new fields can hold the same kind of information.

 

[CharlieSW10]

As you might have seen in the above post I have now got the synch working, using the SQL statement below:

REPLACE INTO rtdtest.products (
ID,
title,
price,
category,
available)
SELECT
product.Product_Code,
product.Product_Description,
product.Price,
product.Category_Name,
product.Is_Active
FROM product where Retailer_Code = "BST"

However I would like to set a flag in the Product table to indicate the record has been synched, the flag will be unset if the record is changed at any point in the future.

I'd like to try and do this within the same statement? Is there any way I can add something to the above statement that will set a field value in the originating Product table for all the records in the recordset that has been written into the rtdtest.products table.

Thanks for any ideas.

Charlie

 

[KarveR]

ok an idea.
add the field mod_time TIMESTAMP(14) to product and the field sync ENUM("1","0") default '0' to rtdtest.

modify your select slightly:

REPLACE INTO rtdtest.products (
  ID,
  title,
  price,
  category,
  available,
  sync)
SELECT
  product.Product_Code,
  product.Product_Description,
  product.Price,
  product.Category_Name,
  product.Is_Active,
  1
FROM product where Retailer_Code = "BST" 
AND mod_time > date_format(date_sub(now(), interval 11 minute),'%Y%m%d%H%i%s');

run every 10 minutes, the sql query is 11 minutes to allow for system a liitle overlap, don't want to miss stuff.

once the job has run, rtdtest.sync will have a value of 1, but really I dont think you need this indicator if you update everything based on mod_time anyway, as all data will be most current.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.