Comparing Tables

My Database is in Mysql 5.0. What I would like to do is to have a script or something that runs at a selected time that compares two tables.Basically what I need is to have two tables exactly the same after the script is run.What would be the best possible solution? Triggers, Creating a function.
LVL 8
thenoneAsked:
Who is Participating?
 
Pigster14IT ConsultantCommented:
So if table1 deletes a record, then you want table2 to still keep that record.
If table1 has a new record, you want table2 to have the new record.
If table1 has fields changed then you want table2 to have that updated information.

Okay, now I see why you mention a trigger.

I would suggest a trigger.
You need a trigger on table1 when a record is added. Whatever record is added, write a sql statement (Select Into) into Table 2.

Then on update, you need to find what field was updated and update that field in sql a sql statement as well.

Thanks.
0
 
Pigster14IT ConsultantCommented:
What is the selected time? Once a day? Once a week?
On open of some application? I think that would hinder how you run your code.

Thanks.
0
 
thenoneAuthor Commented:
Twice a week starting at midnight
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pigster14IT ConsultantCommented:
Well, what about a stored procedure that gets scheduled? Or your function might be fine as well. Just set up a job that runs twice a week.

Then in the stored procedure, you can do the compare on your tables and run update or append statements to get them back together.

Or if only one table changes, you could just copy over the other table, to make sure it's the same.

Thanks.
0
 
thenoneAuthor Commented:
how would I write a stored procedure to do it and timely?
0
 
Pigster14IT ConsultantCommented:
Well, you can do anything really. Whatever you are comfortable with. A trigger, function, stored procedure, etc.
Anything you can put some sql statements in and schedule.

Well, answer these:
Does only one table get updated and the other table is just a copy?
Or
Does both tables get updated and you need to update both tables depending on the differences among them?

Thanks.
0
 
thenoneAuthor Commented:
One is just a copy.
0
 
Pigster14IT ConsultantCommented:
Well, create a new job in SQL Server.
Create a Step and in the command window, put the following

Select * Into Northwind.dbo.NewCustomers
From Northwind.dbo.Customers

Now, if the copy goes on a different server or database you have to change accordingly, but I think you get the idea.
And then just schedule it to run whenever.

Thanks.
0
 
thenoneAuthor Commented:
This is mysql though would it be similar?
0
 
Pigster14IT ConsultantCommented:
I have not done much in mysql so I am not sure. And I don't have it here to test. So much of help am I? Sorry.
Do you have SQL Enterprise Manager in mysql?
0
 
thenoneAuthor Commented:
something similar.So lets say I have table1 and table2.Table2 is the copy.How would the sql statement look?
0
 
Pigster14IT ConsultantCommented:
Select * Into Table2
From Table1

0
 
thenoneAuthor Commented:
Thats it what if there was some deletions in table1?
0
 
Pigster14IT ConsultantCommented:
Well, table1 would not have them and they would then not appear in table2.

I asked <Does only one table get updated and the other table is just a copy?>
Your answer: <One is just a copy.>

So now I am wondering if you meant that table2 is an exact copy of table1 or if table2 keeps an accumulative of what table1 has/had.

Thanks.
0
 
thenoneAuthor Commented:
table1 or if table2 keeps an accumulative of what table1 has/had.

Table1 gets updated deleted etc. Table2 would just reflect on table1 of what is deleted updated and deleted.
0
 
thenoneAuthor Commented:
I really do not want a trigger though becasue of the time factor.In essence that would be the same thing.If I do a timed stored procedure then that would cut it down.
0
 
thenoneAuthor Commented:
If this is possible.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.