• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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.
0
thenone
Asked:
thenone
  • 9
  • 8
1 Solution
 
Pigster14Commented:
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
 
Pigster14Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
thenoneAuthor Commented:
how would I write a stored procedure to do it and timely?
0
 
Pigster14Commented:
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
 
Pigster14Commented:
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
 
Pigster14Commented:
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
 
Pigster14Commented:
Select * Into Table2
From Table1

0
 
thenoneAuthor Commented:
Thats it what if there was some deletions in table1?
0
 
Pigster14Commented:
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
 
Pigster14Commented:
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now