This tip shows you different ways to compare data, datatypes and tables. Solution I will show you different methods to identify changes. Let's say that we have two similar tables in different databases and we want to know what is different: The table names are the same, but the table in database dbtest02 contains an extra row: Let's look at ways we can compare these tables using different methods. In this example, we are comparing 2 tables and the values of 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. Is there another method to compare tables?
Let's use the Except clause to see what we can find. 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: 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, the except will notice the difference.
Here is an example if we update id "" 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 differentness now: Unfortunately it does not create a script to synchronize the tables. Is there a way to compare tables and synchronize results? This can be found in "C: 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 tablediff article. 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 this article: Is there a way to compare the datatypes?
We are going to create a new table named dbo. The query to compare data types of the article and article 1 would be: USE dbtest01 GO select c1.
The query compares the data types from these two tables. We are comparing the table "article" with the table "article2" and showing if any of the datatypes are different. Compare if there are Extra Columns Between 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: USE dbtest01 GO select c2. The different columns are extra1 and extra2.
This is the result of the query: Compare Tables in Different Databases Now let's compare the tables in database dbtest01 and dbtest The following query will show the different tables in dbtest01 compared with dbtest Next Steps There are multiple tools and ways to compare data.
According to your needs you need to select the best method. For more information refer to these links: