How to: Compare and Synchronize the Data of Two Databases
drop table #a drop table #b select * into #a from [databasename1].information_loveallfind.coms a --where table_name = 'aaa' select * into #b from [databasename2].information_loveallfind.coms b -- add linked server name and db as needed --where table_name = 'bbb' select distinct(loveallfind.com_name), loveallfind.com_SCHEMA+ '.' + (loveallfind.com_name) TableName,loveallfind.com_CATALOG DatabaseName from #a a right join #b b on loveallfind.com_NAME = loveallfind.com_NAME and loveallfind.com_SCHEMA = loveallfind.com_SCHEMA where loveallfind.com_name is null-- and . To compare data by using the New Data Comparison Wizard On the SQL menu, point to Data Compare, and then click New Data Comparison. The New Data Comparison wizard appears. Also, the Data Compare window opens, and Visual Studio automatically assigns it a name such as DataCompare1.
This tip shows you different ways to tw data, datatypes and tables using SQL Server. 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 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:.
The left join shows all tablee from the left table dufferent In this example, we are comparing 2 tables and the values of NULL are displayed if there are no matching rows. This can hpw done both ways to see if there are differences the other way around. This query will just return the 3 matching rows. Except shows the difference between two tables the Oracle 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:. 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 "" 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:. There is a nice command line tool used to compare tables. This command line tool is used to compare tables. For more details, refer to this tablediff article. This feature is available in SQL Server and differeng.
Basically it creates system tables that track the changes in your tables that eatabases want to monitor. It does not compare tables, but it tracks the changes in diffedent. We are going to create a new table named dbo.
The difference is that the id is now an int instead of nchar 10 like the other tables. The query compares the data types from these two tables. We are comparing the table "article" with table "article2" how to compare two tables in different databases in sql showing if any of the datatypes are different. Sometimes we need to make sure that two tables contain the same tablex of columns.
To illustrate this we are going to create a table named "article3" with 2 extra columns named extra1 and extra The query compares the different columns between table "article" and "article3". The different columns are extra1 and extra2. This is the result of tk query:. Now let's compare the tables in database dbtest01 and dbtest The following query will show the different tables in dbtest01 compared with dbtest There are great tools to compare tables including data and schemas.
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 differenf inserted in the source records and which all records are updated twk the source table.
Could you please help me? Its amazing article you wrote. I am sure it will help others as wellWelldone!!!!! Thank you very much Daniel! Keep up the excellent work how to do a smokey eye sharing the knowledge! It makes a ton of my earlier attempts at table comparison moot, but Tsbles don't care! An excellent summation. 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.
How to install blow in insulation could compaer the EXCEPT method shown above to show records that tl 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. I liked this post, but this ssl not full fill for the dependent table.
Compar have faced an issue when accidently 1 master table updated satabases source table that has updated multiple table on multiple sal, in some table it uses foreign key relation ship.
How to make a line graph excel we have to identify all the changes related to this one. This changes also replicated ccompare 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!!!! Compare unlimited databases, generate management reports, graphically display database dependencies, SQL server monitor etc etc. I choosed Devart's products for my needs which are much cheaper and the same proffesional. Thank you Daniel for your good article. Excellent page, I have found this already twice when needed this!
Maybe I should now bookmark this. It also works well with NULLs. Hence if you UNION 2 tables with rows each together, if they're compaee, the result will have rows also. You can then look for duplicates on the PKey field t to identify those rows which are different. My original article included some demos ij 3 third party tools, but the editors decided not to promote third party tools. I somewhat agree wtih Marting Borgars. But there are times when those won't wql available.
As Boris points out, they aren't free well worth the price thoughso you may not always have them, especially when working for a small client on a small ih. Some institutions also have how to format usb with diskpart 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. 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. Ways to compare and find differences for SQL Server tables and data. View all my tips. SQL Server tablediff command line utility Recommended Reading.
SQL Server tablediff command line utility. Back To Top Great Cmd line. It would be great if the above would let us exclude columns. Thanks, How to dry artichoke flowers. Dear Daniel Its amazing article you wrote. Please get the solution. Helllo please read this instead of above, i want to compare two tables from different database, suppose both tables have 5 raw in which 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 jn not entire raw then what to do?
Thank you! David McKinney sorry, looks his site is down Boris I couldn't get your how to pass istqb foundation exam to work for Lee Everest's article. A simple method that I use that works quite well and is free is to create a SQL query that looks like the following. If one is familiar with tools that use regular expressions, it can be done quite quickly by starting with the output of 'script table as select' using SMS.
Assuming the keys are the primary keys, it is only necessary to check one of them for each table to null to detect a 'missing row' condition. And it ij amazing what data types compare quite well using '-1' as the default if null. Of course, one has to be smart and if '-1' is a valid data condition then either some other value of checking specifically for 'is null' should be used.
After running the SQL, I usually load it into excel, cut all of the data from 'Test' to differen last column and last row, then paste it after the 'prod' data. Great article, thank you for providing it. Thanks for the interesting article!
Aug 01, · Now, to compare database table schemas, we use a tool dbForge Schema Compare for SQL Server, which is also included in SQL Tools. For this, in SSMS, right-click the first database and in the drop-down menu, select Schema Compare\ Set as Source: Img Selecting the source-base for schema comparison. Oct 21, · Compare SQL Server Data in Tables Using the EXCEPT Clause Except shows the difference between two tables (the Oracle 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. Aug 29, · You can compare the two similar tables or data sets using MINUS operator. It returns all rows in table 1 that do not exist or changed in the other table. Select Id_pk, col1, col2,coln from table1 MINUS Select Id_pk, col1, col2,coln from table2; You can quickly check how many records are having mismatch between two tables.
Whatever the case, you want to be sure that whatever changes have been made whether in your code or somewhere else , the changes in the output are as expected. In other words, you need to be sure that anything that was supposed to change, did, and that anything else remains the same. So, how can you easily do that in SQL Server? Along the way, I hope you learn a few useful techniques.
This code creates the tables Original and Revised that hold customer data. At the moment they are completely different, which you can see since they are small. But what if these tables had thousands or millions of rows?
Enter set-based operations! Relational databases combine set theory with relational calculus. That means that we can use set theory. Remember these set operations? Using these two queries, we can see if the tables are identical or what their differences may be. If the number of rows in the first query INERSECT is the same as the number of rows in the Original and Revised tables, they are identical, at least for tables having keys since there can be no duplicates.
Similarly, if the results from the second query EXCEPT are empty and the results from a similar query reversing the order of the selects is empty, they are equal. Saying it another way, if both sets have the same number of members and all members of one set are the same as all the members of the other set, they are equal. The tables we are working with are keyed so we know that each row must be unique in each table, since duplicate keys are not allowed.
What about non-keyed tables? But the tables are different! Now this query:. These are the three rows that the two tables have in common. However, since each table has 4 rows, you know they are not identical. Here, we take about half the rows of the Original table and insert them into the Revised table.
This query takes some of the rows from the Revised table and inserts them into the Original table using a similar technique, while avoiding duplicates.
Whichever table I put first, I should get 5 rows output. For example:. Depending on the change being implemented, these results may be either good or bad. But at least now you have something to show for your efforts! What if only certain columns are changing? For example, what if in the Revised table, for some customer id, the name or phone number changed?
It would be great to be able to report the rows that changed and also to provide a summary of the number of changes by column and also some way to see what changed between two rows, not just visually, but programmatically. These sample tables are small and narrow. Imagine a table with 40 columns, not 4 and 1 million rows, not Computing such a summary would be very tedious. This shows me that there are 8 rows with the same customer id but different contents and that four of them have different phone numbers, two have different names and two have different addresses.
To that end I can use the system catalog view sys. This view returns a row for each column of an object that has columns, such as views or tables. Each row contains the properties of a single column, e. See the references section for a link to the official documentation where you can find all the details.
Now, there are at least two columns in sys. There are other id columns that may be different as well. When using these techniques in your own work, check the ones that apply. If the two tables really are identical, the above query would return no results.
However, we need to think about the columns that may differ because they refer to other objects. You can eliminate the problem this way:. This script will create a temporary table from sys. Now, we can populate it like this:. You might want to exclude identity columns since they are system generated and are likely to differ between otherwise-identical tables. In the working example though, I explicitly set the customer ids so this does not apply, though it very well might in the next comparison.
Repeat the above logic for the target table The Revised table in the working example. Then you can run:.
In the working example, this will return no results. For the purpose of this article, however, I expect no results. This query mixes up the data in the columns of the Revised table in randomly-selected rows by simple transpositions and arithmetic.
Now that we know the tables are comparable, we can easily compare them to produce some basic difference statistics. Since SQL uses three-value logic True, False and Null , you might have written something like this to compare two columns:. To check if columns from two tables are different. This works of course, but here is a simpler way! On top of that, this does not cause a performance problem or make for a suddenly-complicated execution plan.
The reason is that the sub query is comparing columns from two rows that are being matched, as in a JOIN for example. You can use this technique anywhere you need a simple comparison and columns or variables are nullable. At its heart, this query joins the Original and Revised tables on customer id.
The query uses the technique just described to make things compact and easy to read. The eight rows that differ, differ in specific columns. For each row that differs, we have a CSV list of column names. The leading and trailing commas make it easy to pick out column names, both visually and programmatically. Although there is only one column listed for each customer id, there could be multiple columns listed and in a real-world scenario likely would be.
Since the newly-created temp table has the customer id in it, you can easily do a three-way join between the Original and Revised tables to see the context and content of these changes, e. For a very-wide table, this makes it easy to zero in on the differences, since for each row, you have a neat list of the columns that differ and you can select them accordingly.
I can produce the table of differences I wanted. This is an interesting query because I am not returning anything from the temp diffcols table. Readability is always important, regardless of the language. Help is on the way! Comparing tables or query results is a necessary part of database development. Whether you are modifying an application that should change the result or making a change that should not change the result, you need tools to do this efficiently.
Downloads GetComparisonScript. FROM Original. FROM Revised. FROM sys. WHERE c. CustId ,. CustAddress ,. CustName ,. SET Revised. ON Revised. InOriginal AS. InRevised AS. InBoth AS. WHERE a. CustAddress ' , '' ,. AS cols. INTO diffcols. FROM Original src.
JOIN Revised tgt. ON src. FROM Original o. FROM diffcols d. FROM src. Author Recent Posts. Gerald Britton.