2020. 1. 23. 23:06ㆍ카테고리 없음
SQL Server provides us with different solutions to replicate or archive a database table or tables to another database, or the same database with different names. As an SQL Server Developer or Database Administrator, you may face situations when you need to check that the data in these two tables are identical, and if, by mistake, the data is not replicated between these two tables, you need to synchronize the data between the tables. In addition, if you receive an error message, that breaks the data synchronization or replication process, due to schema differences between the source and destination tables, you need to find an easy and fast way to identify the schema differences, ALTER the tables to make the schema identical in both sides and resume the data synchronization process. In other situations, you need an easy way to get the YES or NO answer, if the data and schema of two tables are identical or not. In this article, we will go through the different ways to compare the data and schema between two tables.
The provided methods in this article will compare tables that are hosted in different databases, which is the more complicated scenario, and can be also easily used to compare the tables located in the same database with different names. Before describing the different methods and tools that can be used to compare the tables data and schemas, we will prepare our demo environment by creating two new databases and create one table in each database, with one small data type difference between these two tables, as shown in the CREATE DATABASE and CREATE TABLE T-SQL statements below. GO Now the testing environment is ready to start describing the data and schema comparison methods. Compare Tables Data Using a LEFT JOIN The LEFT JOIN T-SQL keyword is used to retrieve data from two tables, by returning all records from the left table and only the matched records from the right table and NULL values from the right table when there is no matching between the two tables.
For data comparison purposes, the LEFT JOIN keyword can be used to compare two tables, based on the common unique column such as the ID column in our case, as in the SELECT statement below. ID The previous query will return the common five rows existing in the two tables, in addition to the row that exists in the first table and missing from the second one, by showing NULL values at the right side of the result, as shown below: You can easily derive from the previous result that, the sixth column that exists in the first table is missed from the second table. To synchronize the rows between the tables, you need to insert the new record to the second table manually. The LEFT JOIN method is helpful in verifying the new rows but will not help in the case of updating the columns values. If you change the Address column value of the 5th row, the LEFT JOIN method will not detect that change as shown clearly below: Compare Tables Data Using EXCEPT Clause The EXCEPT statement returns the rows from the first query (left query) that are not returned from the second query (right query).
Also, check out these tool pages: SQL Server Comparison - Data Tools; SQL Server Comparison - Object Tools; Next Steps. Next time you need to compare your database objects don't waste your time doing it manually. Purchase one of these products or download the free version and give it a try. Most if not all of these vendors offer free trial. The result of the comparison between two databases or database projects is analyzed and allows you to apply the change from source database to target, or simply generate T-SQL scripts you can view, modify and run at your control. SQL Server schema compare. Compare and synchronize SQL Server database schemas. Overview Features Resources Gallery Content Compare Roadmap Testimonials. This article describes how a system including a SQL Server database schema compare tool, along with some PowerShell and a scheduler can keep a source control repository in sync and up to.
In other words, the EXCEPT statement will return the difference between two SELECT statements or tables, that helps us easily to compare the data in these tables. The EXCEPT statement can be used to compare the data in the previously created tables, by taking the difference between the SELECT. query from the first table and the SELECT.
query from the second table, using the T-SQL statements below. SELECT. FROM TESTDB2. FirstComTable S The result of the previous query will be the row that is available in the first table and not available in the second one, as shown below: Using the EXCEPT statement to compare two tables is better than LEFT JOIN statement in that, the updated records will be caught in the data differences result.
Assume that we updated the Address of row number 5 in the second table, and checked the difference using EXCEPT statement again, you will see that the row number 5 will be returned with the differences result as shown below: The only disadvantage of using the EXCEPT statement to compare the data in two tables is that you need to synchronize the data manually by writing an INSERT statement for the missing records in the second table. Take into consideration that the two tables that are compared are keyed tables to have the correct result, with a unique key used for comparison. If we remove the ID unique column from the SELECT statement in both EXCEPT statement sides, and list the rest of non-key columns, as in the statement below.
Sql Server Database Tutorial
SELECT FirstName, LastName, Address FROM TESTDB2. FirstComTable S The result will show that only the new records are returned, and the updated ones will not be listed, as shown in the result below: Compare Tables Data Using a UNION ALL GROUP BY The UNION ALL statement can be also used to compare the data in two tables, based on a unique key column. To use the UNION ALL statement to return the difference between two tables, you need to list the columns to compare in the SELECT statement and use these columns in the GROUP BY clause, as shown in the T-SQL query below. COMMIT TRANSACTION Choosing the Update Target option will ask you first for your confirmation to perform the change, as in the message below: After the synchronization, you will see that the data in the two tables will be identical, as shown below: Compare Tables Data Using “dbForge Studio for SQL Server” Third-Party Tool In the SQL Server world, you can find a large number of third-party tools that make the life of the database administrators and developers easy. One of these tools, that make the database administration tasks a piece of cake, is the, that provides us with easy ways to perform the database administration and development tasks. This tool can also help us in comparing the data in the database tables and synchronize these tables. From the Comparison menu, choose New Data Comparison option, as shown below: From the New Data Comparison wizard, specify the source and target database, then click Next: Choose the suitable options from the wide range of available mapping and comparison options and click Next: Specify the name of the table or tables that will participate in the data comparison process.
The wizard will display a warning message in case there are any schema differences between the source and target databases tables. Click Compare to proceed: The final result will show you in detail, the data differences between the source and target tables, with the ability to click to synchronize the source and destination tables, as shown below: Compare Tables Schema Using sys.columns As mentioned at the beginning of this article, to replicate or archive a table, you need to make sure that the schema of the source and target tables is identical. SQL Server provides us with different ways to compare the schema of the tables in the same database or different databases. The first method is querying the sys.columns system catalog view, that returns one row for each column of an object that has a column, with the properties of each column. To compare the schema of tables located in different databases, you need to provide the sys.columns with the table name under the current database, without being able to provide a table hosted in another database. To achieve that, we will query the sys.columns twice, save the result of each query in a temp table and finally compare the result of these two queries using the EXCEPT T-SQL command, as shown clearly below. SELECT.
FROM #DB2Schema The result will show us that, the Address column definition is different in these two tables, with no specific information about the exact difference, as shown below: Compare Tables Schema Using INFORMATIONSCHEMA.COLUMNS The INFORMATIONSCHEMA.COLUMNS system view can be also used to compare the schema of different tables, by providing the table name. Again, to compare two tables hosted in different databases, we will query the INFORMATIONSCHEMA.COLUMNS two times, keep the result of each query in a temp table and finally compare the result of these two queries using the EXCEPT T-SQL command, as shown clearly below. SELECT.
FROM #DB2Schema And the result will be somehow similar to the previous one, showing that the Address column definition is different in these two tables, with no specific information about the exact difference, as shown below: Compare Tables Schema Using dmexecdescribefirstresultset The tables schemas can be also compared by querying the dmexecdescribefirstresultset dynamic management function, that takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement. To compare the schema of two tables, you need to join the dmexecdescribefirstresultset DMF with itself, providing the SELECT statement from each table as a parameter, as in the T-SQL query below. GO The result will be clearer this time, as you can compare by eye, the difference between the two tables, that is the size and type of the Address column, as shown below: Compare Tables Schema Using SQL Server Data Tools SQL Server Data Tools can be also used to compare the schema of tables located in different databases.
By: Last Updated: 2018-05-05 Related Tips: Problem There is often the need to compare both data and database structures from two databases either on the same server or on different servers. Most text editors have a built-in process to allow you to do a diff and identify any differences between the two files, but comparing data is not quite that easy. Using this diff process is great if you only want to compare a few files, but what if you need to scan your entire database to look for differences? As with anything you can always create your own application to automate the comparison process. You could cursor through all of the tables read the T-SQL from one server, get the T-SQL for the same object from the other server and do a comparison. To compare the data you can write scripts that read row by row and do a comparison of the data. But why bother when there are so many products out there already.
Solution Luckily a lot of products already exist. These products allow you to compare database objects, data, DTS scripts, servers, etc. In addition, these products are not all that expensive either. Some of the products are under $100 US and I also found a company that offers a free version, see below. Post a comment or let the author know this tip helped. All comments are reviewed, so stay on subject or we may delete your comment.
Note: your email address is not published. Required fields are marked with an asterisk (.).Name.Email Email me updates. NOTE. If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting. Signup for our newsletter I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors.
I have read the and understand I may unsubscribe at any time. Friday, June 29, 2018 - 2:54:20 PM - Greg Robidoux The tools look at the DDL and DML code to figure out what is the same and different. Friday, June 29, 2018 - 2:45:00 PM - Ernest Are the tools only using the schema for comparison?? Friday, February 17, 2017 - 7:02:09 AM - Nat great list of tools, thanks! I use dbForge Schema Compare and am very satisfied! Quickly compare SQL Server databases, analyze differences and perform error-free schema synchronization - this all about dbForge tool! Monday, September 12, 2016 - 3:48:19 AM - Phil Grayson Hi, we have a free tool for comparing two database instances of SQL Server including users, plan guides, trace flag, cores, memory, agent jobs etc.
Tuesday, August 20, 2013 - 5:03:02 PM - Greg Robidoux @Binu - have you talked to ApexSQL to see if they have a solution to automate what you are doing with their tool? Thursday, August 15, 2013 - 12:31:52 PM - Binu Hi, I have got a new task for migrating SQL objects from VSS to TFS. Some objects are there in both VSS and TFS. Have to compare the objects in both VSS and TFS and find out the differences.
Could you please suggest a way for doing this. For now, we are scripting out objects from VFS and with the help of APEXDiff tool we are manually comparing every single object with object in TFS.How to automate this?
Database Comparison Tool Sql Server
Thanks in advance.Binu Wednesday, May 22, 2013 - 8:50:27 AM - SergeiK Sorry for the mistake in URL. Devart's SQL Schema Compare is available here: Wednesday, May 22, 2013 - 8:42:27 AM - SergeiK I wonder why Devart's products are not mentioned here. I'm sure they must be observed here. Try dbForge Data and Schema Compare tools – the most professional sql comparison tools I've ever used! SQL Data Compare: SQL Schema Compare: The price is low but the quality is great! Free trial s are available!
Free Sql Compare Tool
Tuesday, November 20, 2012 - 10:18:40 AM - VC Hi Greg, I prefer this one as it can accomodate Ref- Cheers, VC.