Export Data

[Vamsidhar]

Hi All:

I have a question with regards to exporting data from one database to another.

Let me briefly explain the set up I have.

I have two databases of the same structure.

Live Database.

Archival Database.

Periodically I have to move data from live database to archival database. Currently I have my own scripts to do this. But the problem I have is that the database schema keeps changing from time to time and the scripts have to me maintained accordingly. I want to know if there is a way to export data from the live database (source) to archival database (target) using the export feature such that:

I can join tables in the where clause during the data synchronization process.

I see that the where clause feature in data sync wizard can be used to select some rows from the table. But what I need is to be able to join tables using the where clause to select the rows that match the given criteria.

Please let me know what you think about it.

Thanks,

Vamsi.

[Shadow]

If I understood your problem properly, then the answer is – unfortunately – no, data sync wizard is not suitable for your needs.

First of all, data sync wizard works only if the synced tables' structures are identical. SQLyog has a structure sync wizard, but that cannot be scheduled and needs manual intervention anyhow. Secondly, data sync wizard cannot sync data using complex queries including joins. All you can do is to specify a WHERE clause. Data sync wizard, however, will be further developed. You could participate in testing SQLyog MAX and try to convince Ritesh to incorporate the features you require.

[peterlaursen]

a “DB structure comparison tool” generating a script containing the SQL necessary to make one DB identical to another …

yeah!

[Shadow]

You mean, a tool that creates alter table statements instead of completely dropping and then recreating a table?

[peterlaursen]

> Shadow … was that a Q for me ??

I'm not so much an SQL-expert to decide how that should be implemented. I think convenience and data securety pull in different directions. In all circumstances an option to save old data should be implemented.

But with big corporate databases and when performing operations over the internet an “alter structure” solution probably would be preferrable. But when it comes to changed relationsships (and maybe changed relationships involving newly created tables) it's not so simple! And foreign keys and stored procedures complicate further …

[Shadow]

@peterlaursen: Yes, that was a question for you! Of course, a db structure may not be completely altered by an automatic tool, but in simpler cases it might come handy. I know the limits of ALTER TABLE statements, especially in older versions of MySql, that is why I asked what you had in your mind. Though you had an alternative approach…

[Vamsidhar]

Thank you all for the replies.

I think for now I would have to get along with the scripts I have. As suggested by shadow, I will participate in testing SQLyog MAX and try to convince Ritesh to incorporate the features I need.

Thanks again.

Vamsidhar.

[Author]

Posts