SQL query to compare two tables in 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 OCCURING 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.

Structured Query Language supported by RDBMS(Relation Database Management Systems) such as Oracle, Mysql, PostgreSQL, and SQL Server. All relational database system support objects such as databases, tables, schema, indexes, views, functions, procedures, and many more.

RDBMS system performs best when the schema is on write. To make these systems perform well on the schema on reading we have indexes that help in improving the data read or search performance.

Comparing indexes of tables from two different databases requires their data and schema structure to be compared first. In a hierarchy of database management systems, we have a schema, databases, tables, columns then indexes.

Consider two different databases “Department_HR”  and “Department_CR” 

Step 1: Creating users for the databases.

create user source PASSWORD 's';
ALTER USER source WITH SUPERUSER;

create user target PASSWORD 't';

Step 2: Creating Database “Department_HR” and “Departement_CR”

create database Department_HR OWNER source;

create database Department_CR OWNER target;

Step 3: Create table “Employees”  in Database “Department_HR” and “Department_CR”

Create table Department_HR.Employees
(  EMPLOYEE_ID int,
  FIRST_NAME varchar(120),
  LAST_NAME varchar(120),
  EMAIL varchar(120),
  HIRE_DATE date,
  JOB_ID  varchar(120),
  SALARY float,
  COMMISSION_PCT float,
  MANAGER_ID int,
  DEPARTMENT_ID int
);

Create table Department_CR.Employees
(  EMPLOYEE_ID int,
  FIRST_NAME varchar(120),
  LAST_NAME varchar(120),
  EMAIL varchar(120),
  HIRE_DATE date,
  JOB_ID  varchar(120),
  SALARY float,
  COMMISSION_PCT float,
  MANAGER_ID int,
  DEPARTMENT_ID int
);

Step 4: Insert data in the department_HR.employees and department_CR.employees tables

insert into employees values(100,'Steven',
'King','SKING','17-Jun-03','AD_PRES',24000, null ,90);
insert into employees values(101,'Neena','
Kochhar','NKOCHHAR','21-Sept-05','AD_VP',17000,100,90);
insert into employees values(102,'Lex','DeHaan',
'LDEHAAN','13-Jan-01','AD_VP',17000,100,90);
insert into employees values(103,'Alexander',
'Hunold','AHUNOLD','03-Jan-06','IT_PROG',9000,102,60);
insert into employees values(104,'Bruce','
Ernst','BERNST','21-May-07','IT_PROG',6000,103,60);
insert into employees values(105,'David',
'Austin','DAUSTIN','25-Jun-05','IT_PROG',4800,103,60);
insert into employees values(106,'Valli',
'Pataballa','VPATABAL','05-Feb-06','IT_PROG',4800,103,60);
insert into employees values(107,'Diana',
'Lorentz','DLORENTZ','07-Feb-07','IT_PROG',4200,103,60);
insert into employees values(108,'Nancy',
'Greenberg','NGREENBE','17-Aug-02','FI_MGR',12008,101,100);

Step 5: Select the inserted data in the department_HR.employees and department_CR.employees table

select * from department_HR.employees;

select * from department_CR.employees;

Step 6: Compare the indexes from both databases we must have indexes on the table’s column

create index indx_dept_emp_id on
department_HR.employees(employee_id);
create index indx_dept_manager_id 
on department_HR.employees(manager_id);

create index indx_dept_emp_id on 
department_CR.employees(employee_id);
create index indx_dept_manager_id on 
department_CR.employees(manager_id);

Step 7: Create a link between two different databases to compare the indexes

Login to database  Source database Department_HR and create a DB LINK

• Create an extension to establish a connection between two databases

CREATE EXTENSION dblink;

• Create a server to make a connection to the target database.

CREATE SERVER server_Department_CR_remote 
FOREIGN DATA WRAPPER 
 dblink_fdw OPTIONS (host 'localhost', 
 dbname 'Department_CR', port '1521');

•  Create user mapping from the source database for the target database.

CREATE USER MAPPING FOR Department_HR SERVER
 server_Department_CR_remote OPTIONS (user 'target',password 't');

•  Provide select access to the source database on the remote server.

GRANT USAGE ON FOREIGN SERVER server_Department_CR_remote TO source;

• dblink_connect function to establish the connection from the source to the target DB.

SELECT dblink_connect('myconn', 
'dbname=department_cr port=1521 
host=localhost user=target password=t');
 

Step 8: Query to compare the indexes.

# Login to source database

select distinct i1.index_name from user_ind_columns i1
where table_name='Department_HR' and not exists 
(select * from user_ind_columns i2 where 
i2.table_name = 'Department_CR' and i1.column_name = i2.column_name
and i1.column_position = i2.column_position);

Output:

Index_name
----------
indx_dept_emp_id 
indx_dept_manager_id 
 

How to compare two tables from different database in Oracle?

How to compare two tables in SQL in different databases?

How to compare two tables in different schemas in Oracle?

How to compare two columns in two different tables in Oracle?