SQL how to compare two tables for same data content?
How do I write an SQL query to check if TableA and TableB (which have identical primary keys) contain exactly the same values in every column?
It means that these two tables have exactly the same data.
21 Answers 21
You should be able to «MINUS» or «EXCEPT» depending on the flavor of SQL used by your DBMS.
If the query returns no rows then the data is exactly the same.
Using relational operators:
Change EXCEPT to MINUS for Oracle.
Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:
- Expressions in parentheses
- INTERSECT
- EXCEPT and UNION evaluated from left to right.
dietbuddha has a nice answer. In cases where you don’t have a MINUS or EXCEPT, one option is to do a union all between the tables, group by with all the columns and make sure there is two of everything:
Will return all ID’s that are the SAME in both tables. To get the differences change EXISTS to NOT EXISTS.
Taking the script from onedaywhen, I modified it to also show which table each entry comes from.
Enhancement to dietbuddha’s answer.
Clever approach of using NATURAL FULL JOIN to detect the same/different rows between two tables.
Example 1 — status flag:
Example 2 — filtering rows
just to complet, a proc stored using except method to compare 2 tables and give result in same table with 3 errors status, ADD, DEL, GAP table must have same PK, you declare the 2 tables and fields to compare of 1 or both table
Just use like this ps_TableGap ‘tbl1′,’Tbl2′,’fld1,fld2,fld3′,’fld4’fld5’fld6’ (optional)
You can find differences of 2 tables using combination of insert all and full outer join in Oracle. In sql you can extract the differences via full outer join but it seems that insert all/first doesnt exist in sql! Hence, you have to use following query instead:
Although using ‘OR’ in where clause is not recommended and it usually yields in lower performance, you can still use above query if your tables are not massive. If there is any result for the above query, it is exactly the differences of 2 tables based on comparison of fields 1,2,3,4. For improving the query performance, you can filter it by date as well(check the commented part)
Result is null, but sources are different!
I had this same issue in SQL Server and wrote this T-SQL script to automate the process (actually this is the watered-down version, mine wrote all the diff to a single table for easy reporting).
Update ‘MyTable’ and ‘MyOtherTable’ to the names of the tables you wish to compare.
Сравнить две таблицы в MySQL
Добавить в избранное
Главное меню » Сравнить две таблицы в MySQL
При переносе данных нам часто приходится сравнивать две таблицы, чтобы определить запись в одной таблице, у которой нет соответствующей записи в другой таблице.
Например, у нас есть новая база данных, схема которой отличается от устаревшей базы данных. Наша задача — перенести все данные из устаревшей базы данных в новую и убедиться, что данные были перенесены правильно.
Чтобы проверить данные, нам нужно сравнить две таблицы, одну в новой базе данных и одну в устаревшей базе данных, и идентифицировать несопоставленные записи.
Предположим, у нас есть две таблицы: my_table и you_table. Следующие шаги сравнивают две таблицы и идентифицируют несопоставленные записи:
Во-первых, используйте оператор UNION для объединения строк в обеих таблицах; включать только столбцы, которые нужно сравнить. Возвращенный набор результатов используется для сравнения.
Во-вторых, сгруппируйте записи на основе первичного ключа и столбцов, которые необходимо сравнить. Если значения в столбцах, которые необходимо сравнить, идентичны, COUNT(*) возвращается 2, в противном случае COUNT(*) возвращается 1.
Смотрите следующий запрос:
Если значения в столбцах, участвующих в сравнении, идентичны, строка не возвращается.
Пример сравнения двух таблиц в MySQL
Давайте посмотрим на пример, который имитирует шаги выше.
Сначала создайте 2 таблицы с похожей структурой:
Во-вторых, вставьте некоторые данные в таблицы my_table и you_table:
В-третьих, сравните значения id и столбца title обеих таблиц:
Возвращенных строк не будет, потому что нет несоответствующих записей.
В-четвертых, вставьте новую строку в таблицу you_table:
В-пятых, выполните запрос, чтобы снова сравнить значения столбца заголовка в обеих таблицах. Новая строка, которая является несопоставленной строкой, должна вернуться.
В этой статье вы узнали, как сравнивать две таблицы на основе определенных столбцов, чтобы найти несопоставленные записи.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Ways to compare and find differences for SQL Server tables and data
Sometimes we need to compare SQL Server tables and/or data to know what has changed. This article shows different ways to compare data, datatypes and table structures when using SQL Server.
Solution
I will cover different methods to identify changes by using various SQL queries as well as a couple development tools.
Let’s say we have two similar tables in different databases and we want to know what is different. Here is a script that creates sample databases, tables and data.
The T-SQL code generates 2 tables in different databases. The table names are the same, but the table in database dbtest02 contains an extra row as shown below:
Let’s look at ways we can compare these tables using different methods.
Compare SQL Server Data in Tables Using a LEFT JOIN
With a LEFT JOIN we can compare values of specific columns that are not common between two tables.
For example, with this SELECT statement:
The result set from the LEFT JOIN shows all rows from the left table «dbtest02.dbo.article», even if there are no matches in table «dbtest01.dbo.article»:
In this example, we are comparing 2 tables and the NULL are displayed if there are no matching rows. This method works to verify new rows, but if we update other columns, the LEFT JOIN does not help.
This can be done both ways to see if there are differences the other way around. This SQL statement will just return the 3 matching rows.
SQL Server Data Comparison in Tables Using the EXCEPT Clause
Except shows the difference between two tables (the Oracle DBMS guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let’s see the differences between the two tables:
Now let’s run a query using except:
The except returns the difference between the tables from dbtest02 and dbtest01:
If we flip the tables around in the query we won’t see any records, because the table in database dbtest02 has all of the records plus one extra.
This method is better than the first one, because if we change values for other columns like the type and cost, EXCEPT will notice the difference.
Here is an example if we update id «001» in database dbtest01 and change the cost from «40» to «1». If we update the records and then run the query again we will see these differences now:
Unfortunately it does not create a script to synchronize the tables.
Compare SQL Server Data in Tables Using the Tablediff Tool
There is a free command line tool used to compare tables. This can be found in «C:\Program Files\Microsoft SQL Server\110\COM\» folder. This command line tool is used to compare tables. It also generates a script with the INSERT, UPDATE and DELETE statements to synchronize the tables. For more details, refer to this table diff article.
Compare SQL Server Data in Tables Using Change Data Capture (CDC)
This feature is available in SQL Server 2008 and later. You need to enable this feature and you also need to have SQL Server Agent running. Basically it creates system tables that track the changes in your tables that you want to monitor. It does not compare tables, but it tracks the changes in tables.
For more information, refer to these Change Data Capture (CDC) tips.
Compare SQL Server Data Types Between Two Tables
What happen if we want to compare the data types? Is there a way to compare the datatypes?
Yes, we can use the [INFORMATION_SCHEMA].[COLUMNS] system views to verify and compare the information. We are going to create a new table named dbo.article2 with a column with different data type than the dbo.article table:
The difference is that the id is now an int instead of nchar(10) like the other tables.
The query to compare data types of tables article and article1 would be:
The results are as follows:
The query compares the data types from these two tables. All the information of the columns can be obtained from the [INFORMATION_SCHEMA].[COLUMNS] system view. We are comparing the table «article» with table «article2» and showing if any of the datatypes are different.
Compare if there are Extra Columns Between SQL Server Database Tables
Sometimes we need to make sure that two tables contain the same number of columns. To illustrate this we are going to create a table named «article3» with 2 extra columns named extra1 and extra2:
In order to compare the columns I will use this query:
The query compares the different columns between table «article» and «article3». The different columns are extra1 and extra2. This is the result of the query:
Compare SQL Server Tables in Different Databases
Now let’s compare the tables in database dbtest01 and dbtest02 with UNION logic (Learn the difference between UNION and UNION ALL) and a subquery with NOT IN logic. The following query will show the different tables in dbtest01 compared with dbtest02:
Compare schemas using SSDT
SQL Server Data Tools allows to compare schemas of two different tables using the SQL Server Database Project. It can generate scripts that will allow you to synchronize data with some few clicks. Let’s take a look to it.
1 — In the database project, go to the Solution Explorer and right click on the database and select the Schema Compare option to compare the tables:
2 — In the Select Target Schema, press the Select Connection to select the table destination to be compared with the table in the source. You will be able to select an existing connection or create a new one:
3 — Next, press the compare button and it will show the difference. It will show the tables to add or the tables to remove:
4 — The tool will show you the entire T-SQL script that you can apply or modify according to your requirements in the tables:
SSIS Lookup option
This option is really popular in ETLs (Extraction Transformation Loads). The Lookup is an SSIS transformation task that allows to lookup data using joins using a dataset. The lookup allows you to detect changed data between 2 tables. In the following example, I will compare the tables.
1 — In the SSIS project, double click the Data Flow Task to create it in the Control Flow in order to create a Data Flow sequence:
2 — In the Data Flow, drag and drop the OLE DB Source with the source table to compare, the Lookup task and 2 OLE DB Destination tasks:
3 — In the Lookup you can fully store the information in cache or connect partially. We also have the no cache option. The full cache mode option will store the data in the SSIS cache. The partial cache will only store on cache values as each distinct value is found in the data flow. Each distinct value will be obtained from the specific source:
4 — The task will allow you to define the keys to compare and the lookup columns with the output alias used:
As you can see, the Lookup allows to lookup and compare data. You can then get what data was changed between 2 tables.
Third Party Tools
There are great tools to compare tables including data and schemas. You can use Visual Studio or use other SQL Server Comparison tools.
Next Steps
- There are multiple tools and ways to compare data and schemas. Select the method that works best for your needs based on the above queries.
- For more information refer to these links:
- Compare SQL Server Datasets with INTERSECT and EXCEPT
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
- Using MERGE in SQL Server to insert, update and delete at the same time
- SQL Server Cursor Example
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.Article Last Updated: 2021-10-21
Comments For This Article
Friday, February 19, 2021 — 6:39:38 AM — Peter Vella Back To Top (88265) Great Cmd line., The problem with other tools is that the tables have to be indexed. It would be great if the above would let us exclude columns. Monday, May 13, 2019 — 8:52:21 AM — db042188 Back To Top (80057) pls use caution with the except statement. it drops duplicates automatically from the left and right. in biz intelligence that isnt acceptable as the dupes are either necessary or an error that needs to be caught.
I have done this using powershell and you can also use this :
Thanks Daniel for this article. I have a requirement to find the difference between two tables of same data but the primary keys will be different, so I have a difficulty in identifying which all records are inserted in the source records and which all records are updated in the source table. Could you please help me?
I tried MERGE and EXCEPT, but not much luck.
Its amazing article you wrote. I am sure it will help others as well , Welldone .
Thank you very much Daniel! Well written & easy to understand and follow plus thorough as most articles are on this site. Keep up the excellent work of sharing the knowledge! 🙂
THANK YOU THANK YOU THANK YOU! I never knew of the EXCEPT keyword. It makes a ton of my earlier attempts at table comparison moot, but I don’t care! An excellent summation.
when i run query in sql server 2012 it join 2 tables but all field contain same data display.
Please get the solution .
Hi Jayesh, this can be done but it takes a lot of coding to figure out which rows are different and then which columns are different. You could use the EXCEPT method shown above to show records that are different, but then you will need to take the results from that and write a process to compare column by column. I don’t think we have a tip that does this, so maybe someone could write a tip.
Helllo please read this instead of above,
i want to compare two tables from different database, suppose
both tables have 5 raw
table 1 and table 2 has 4 raw exactly same,
but fifth raw has minor change in one column value
if i want to to see only that value not entire raw then
red gate can’t give specic difffrent column value
why do we use procedure
I liked this post, but this will not full fill for the dependent table. I have faced an issue when accidently 1 master table updated in source table that has updated multiple table on multiple server, in some table it uses foreign key relation ship. Now we have to identify all the changes related to this one. This changes also replicated to diffrent server. We were thinking can we do with this approach but did not found solution on the spot. We have worked 1 by one table then we sync the table.
If you developer think it can also generate script for dependent table please share. Thanks a lot .
Thanks a lot. Very useful.
I recomment SQL Negotiator Pro from Aphilen software www.aphilen.com they offer a free version which may suffice your needs
The full blown version comes with the ability to
Compare unlimited databases, generate management reports, graphically display database dependencies, SQL server monitor etc etc
Answer: May be because Red Gate’s Tool costs about 500 $ ?
I choosed Devart’s products for my needs which are much cheaper and the same proffesional.
For this need you can use SQL Data Compare from Devart ( http://www.devart.com/dbforge/sql/datacompare/)
Thank you Daniel for your good article.
Excellent page, I have found this already twice when needed this! Maybe I should now bookmark this.
@David McKinney
sorry, looks his site is down. but when I tested this query below, it was the fastest:
SELECT MIN(id) AS id, col1, col2, col3 FROM (SELECT id, col1, col2, col3 FROM tbl1 UNION ALL SELECT id, col1, col2, col3 FROM tbl2) X GROUP BY col1, col2, col3 HAVING COUNT(1)=1
It also works well with NULLs. He does Union ALL, but HAVING COUNT(1)=1 will output only records not found in one of the tables, so matches will be suppressed
@Boris I couldn’t get your link to work (for Lee Everest’s article).
I have a method using UNION as well but not UNION ALL
It relies on the fact that
select ‘alpha’ UNION select ‘alpha’ will only return 1 row (and not 2). Hence if you UNION 2 tables with 1000 rows each together, if they’re identical, the result will have 1000 rows also. You can then look for duplicates on the PKey field(s) to identify those rows which are different.
There is already a pretty nice article related to third party tools to compare data
Personally I love SQL Data Compare from Redgate and SQL Data Compare from devart.
My original article included some demos about 3 third party tools, but the editors decided not to promote third party tools.
Great article, thank you for providing it.
I somewhat agree wtih Marting Borgars. I am a big fan of Red Gate’s SQL Data Compare and Red Gate’s SQL Compare when looking at schemas, I have been extremely happy with them.
But there are times when those won’t be available. As Boris points out, they aren’t free (well worth the price though), so you may not always have them, especially when working for a small client on a small project. Some institutions also have very lengthy validation procedures for installing software due to security concerns.
It is also good to be able to do it «by hand» just for the sake of having a thorough knowledge base and understanding of how things work, even if you will use the tools most of the time.
Thanks for the interesting article!
Red Gate’s SQL Data Compare tool is nice indeed and we use it at work but it is not free so it is great to see alternatives.
Some time ago I bookmarked very fast and cool method, described by Lee Everest here
The idea is to use query like that:
SELECT MIN(id) AS id, col1, col2, col3 FROM (SELECT id, col1, col2, col3 FROM tbl1 UNION ALL SELECT id, col1, col2, col3 FROM tbl2) X GROUP BY col1, col2, col3 HAVING COUNT(1)=1
Как сравнить 2 таблицы sql
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. Here we are going to see how to Compare and Find Differences Between Two Tables in SQL
Here, we will first create a database named “geeks” then we will create two tables “department_old” and “department_new” in that database. After, that we will execute our query on that table.
Creating Database:.
Use the below SQL statement to create a database called geeks:
Using Database :
Table Definition for department_old table:
Add values into the table:
Use the below query to add data to the table:
To verify the contents of the table use the below statement:
ID SALARY NAME DEPT_ID 1 34000 ANURAG UI DEVELOPERS 2 33000 HARSH BACKEND DEVELOPERS 3 36000 SUMIT BACKEND DEVELOPERS 4 36000 RUHI UI DEVELOPERS 5 37000 KAE UI DEVELOPERS The result from SQL Server Management Studio:
Table Definition for department_new table:
Add values into the table:
Use the below query to add data to the table:
To verify the contents of the table use the below statement:
ID SALARY NAME DEPT_ID 1 34000 ANURAG UI DEVELOPERS 2 33000 HARSH BACKEND DEVELOPERS 3 36000 SUMIT BACKEND DEVELOPERS 4 36000 RUHI UI DEVELOPERS 5 37000 KAE UI DEVELOPERS 6 37000 REHA BACKEND DEVELOPERS Output:
Comparing the Results of the Two Queries
Let us suppose, we have two tables: table1 and table2. Here, we will use UNION ALL to combine the records based on columns that need to compare. If the values in the columns that need to compare are the same, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1.
Syntax:
Example:
Output:
If values in the columns involved in the comparison are identical, no row returns.