Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to verify if two tables have exactly the same data?

Tags:

sql

mysql

Basically, we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema.

In the beginning, both tables (original table and backup table) contains exactly the same set of data. After some time for some reason, I need to verify whether dataset in the original table has changed or not.

In order to do this, I have to compare the dataset in the original table against the backup table.

Let's say the original table has the following schema:

create table LemmasMapping (    lemma1 int,    lemma2 int,    index ix_lemma1 using btree (lemma1),    index ix_lemma2 using btree (lemma2) ) 

How could I achieve the dataset comparison?

Update: the table does not have a primary key. It simply stores mappings between two ids.

like image 459
SiLent SoNG Avatar asked Jan 25 '10 01:01

SiLent SoNG


People also ask

How can I get matching records from two tables?

(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.

How do you find common data in two tables in SQL?

If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records. If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.


1 Answers

You can just use CHECKSUM TABLE and compare the results. You can even alter the table to enable live checksums so that they are continuously available.

CHECKSUM TABLE original_table, backup_table; 

It doesn't require the tables to have a primary key.

like image 174
Josh Davis Avatar answered Sep 16 '22 16:09

Josh Davis