How To Get The Differences Of Two Databases With SQL Server?
It’s important to know the distinctions between two databases in the realm of database administration. SQL Server provides strong tools and approaches to assist you accomplish this work, whether you’re moving data, doing data validation, or just making sure consistency. With code examples, we will examine how to use SQL Server to obtain the differences between two databases in this blog article.
Prerequisites
Before we dive into the code examples, make sure you have the following in place:
- SQL Server Management Studio (SSMS) installed.
- Access to the two databases you want to compare.
Method 1: Using JOINs and UNIONs
We’ll start with a classic method using JOINs and UNIONs to compare two tables. This method helps identify new records and deleted records.
Step 1: Find New Records
SELECT * FROM Database2.dbo.YourTable WHERE PrimaryKeyColumn NOT IN (SELECT PrimaryKeyColumn FROM Database1.dbo.YourTable)
Step 2: Find Deleted Records
SELECT * FROM Database1.dbo.YourTable WHERE PrimaryKeyColumn NOT IN (SELECT PrimaryKeyColumn FROM Database2.dbo.YourTable)
Method 2: Using EXCEPT and INTERSECT
Another powerful way to compare two sets of data in SQL Server is by using the EXCEPT and INTERSECT operators. These operators help you find differences and commonalities between two result sets.
Step 1: Find New Records
SELECT * FROM Database2.dbo.YourTable EXCEPT SELECT * FROM Database1.dbo.YourTable
Step 2: Find Deleted Records
SELECT * FROM Database1.dbo.YourTable EXCEPT SELECT * FROM Database2.dbo.YourTable
Method 3: Using SQL Server Data Tools (SSDT)
SQL Server Data Tools (SSDT) is an integrated development environment that provides a database project for managing database schema and data. You can use it to perform schema and data comparisons between databases.
- Open SQL Server Data Tools.
- Create or open a database project.
- Right-click on the project, select “Schema Compare.”
- Configure the source and target databases.
- Click the “Compare” button to view differences.
Conclusion
For database administrators and developers, comparing two databases in SQL Server is a basic activity. The techniques described in this blog article provide many angles for locating newly added entries, records that have been removed, and other variations between databases. Select the approach that best satisfies your requirements and the degree of difficulty of your data comparison assignment.
Consider that these are only the fundamentals. SQL Server is a flexible tool for managing your database activities since it provides much more sophisticated tools for database comparison and synchronization.
Comment (1)
Hello,
Thank you for the article!
Let me add my 2 cents..
1) Method 1 SQL works only for single-column primary key. For composite key we need to use JOIN (like it is mentioned in the method title)
2) Method 2 SQL has couple of issues:
a) Usually we identify row by its primary key value, as it was done for the Method 1. EXCEPT compares data by all columns and if we have same ID in both tables with some changed value of non-ID column, then such a row is returned by EXCEPT but it should be counted as changed, not as new
b) Both tables should have the same columns, in the same order and with compatible data types. Otherwise we may need to select explicit column list and cast/convert data type if necessary
3) Both methods 1 and 2 SQL examples are applicable only for databases located on the same server, to make them work for cross-server scenarios we need to setup linked servers
4) There is a little kind of inconsistency between these methods, methods 1 and 2 are about the DATA and method 3 is about the SCHEMA. To compare Data using SSDT, we need to choose Tools -> SQL Server -> New Data Comparison (for VS 2019 and 2022). What’s good is that SSDT data compare has no limitations I mentioned for methods 1 and 2.