How to Compare Data in All Tables in Two Different Databases in Oracle?

Oracle is a versatile database and it is highly secured and hence to a large extent used in banking and insurance applications. Though NoSQL database dominates in many industries, still oracle has its own significance because most the legacy applications still like to go with oracle like RDBMS only.  Distributed systems will have the usage of 2 or more oracle databases and hence there occurs the scenario of comparing data in all tables in different databases of oracle. Similarly, SQL Server is a versatile most wanted RDBMS and because of its security features, it is highly used.

Let us see how to compare data in all tables in two different databases in SQL Server:

Step 1: Create databases for  employee i.e employeeData 1 and employeeData 2.

Query:

-- employeeData1 database is created
CREATE  DATABASE employeeData1; 
-- Making employeeData1 as active database
USE employeeData1; 

-- Create a table named employees under employeeData1 
CREATE TABLE employees 
( EMPLOYEEID int NOT NULL,   
  EMPLOYEENAME varchar(50) NOT NULL,   
  EMPLOYEECITY varchar(50)   
)
-- employeeData2 database is created
CREATE  DATABASE employeeData2; 
-- Making employeeData2 as active database

USE employeeData2; 
GO
CREATE TABLE employees 
( EMPLOYEEID int NOT NULL,   
  EMPLOYEENAME varchar(50) NOT NULL,   
  EMPLOYEECITY varchar(50)   
)

Step 2: Insert a values in database.

Query:

GO
INSERT INTO employees (employeeId,employeeName,employeeCity) 
VALUES (1,'XXX','CHENNAI')
SELECT * FROM employeeData2.dbo.employees;

Output:

employeeData2

Step 3: Now let us see the comparison of data between this 2 different database of the employees table.

• Using INTERSECT:

Query:

--INTERSECT - IT WILL DISPLAY  
-- ONLY COMMONLY OCCURRING ROWS IN BOTH TABLES

SELECT * FROM employeeData1.dbo.employees INTERSECT
SELECT * FROM employeeData2.dbo.employees;

Output:

• Using UNION:

Query:

--UNION - WILL COMBINE ALL THE ROWS 
--IN BOTH TABLES BUT IGNORES DUPLICATES
SELECT * FROM employeeData1.dbo.employees UNION
SELECT * FROM employeeData2.dbo.employees;

Output:

• Using UNION ALL:

Query:

-- UNION ALL - WILL COMBINE ALL THE ROWS IN 
-- BOTH TABLES BUT WILL HAVE  DUPLICATES AS WELL.
SELECT * FROM employeeData1.dbo.employees UNION ALL
SELECT * FROM employeeData2.dbo.employees;

Output:

• Using EXCEPT:

Query:

-- EXCEPT - IT WILL DISPLAY  ONLY 
-- UNCOMMON ROWS OF BOTH TABLES
SELECT * FROM employeeData1.dbo.employees EXCEPT
SELECT * FROM employeeData2.dbo.employees;

Output:

Only Uncommon rows are present

Conclusion:

By using INTERSECT, UNION, UNION ALL, and EXCEPT, we can compare the data for a single database or even with the different database as well.