Oracle stored procedure to compare data in two tables

Hi, I would like to compare these two tables using stored procedures. table1 SQL> select * from temp; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON table 2 SQL>select * from tmpy_emp; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 IT VIRGINIA 40 OPERATIONS BOSTON any other method than using minus query. I want it to be in stored procedure. create or replace procedure compare IS cursor cr_emp IS select * from temp; cursor cr_tmpy IS select * from tmpy_emp; v_cr_emp cr_emp%rowtype; v_cr_tmpy cr_tmpy%rowtype; BEGIN open cr_emp; open cr_tmpy; fetch cr_emp into v_cr_emp; fetch cr_tmpy into v_cr_tmpy; loop if v_cr_emp = v_cr_tmpy then dbms_output.put_line('same'); else dbms_output.put_line('not same'); end if; end loop; close cr_emp; close cr_tmpy; END; how far is this procedure correct for aboove question.

Table of Contents Show

Read these next...
What cybersecurity podcasts do you recommend listening to?
Auto-Empty Deleted Items Folder
How to redirect a USB license key over RDP
Snap! -- Metaverse, CISA Warnings, Solar Eclipse, Cars, Drugs, NASA Studies UFOs
Service accounts...
How can I compare two tables in Oracle database?
How do you compare values in two tables?
How do you compare data between two tables in different databases?
How can I compare two table values in SQL?

Read these next...

What cybersecurity podcasts do you recommend listening to?
Security
November is right around the corner, meaning Cybersecurity Month is almost ending. But, as we all know, the defense against the digital dark arts is essential all year long. One way members keep up is by reading the Snap! daily and engaging with oth...
Auto-Empty Deleted Items Folder
Collaboration
Hello. Does anyone know of a way to automatically empty the deleted items folder in a shared mailbox? I'd like to auto-delete any email from the deleted items folder that has been there for more than 7 days. Is this possible? This is in an Office 365 envi...
How to redirect a USB license key over RDP
Windows
I have an application that is running on a work pc with the dongle inserted. The application requires the licensing dongle located in a usb socket. Locally, the application works fine and the dongle is visible to the app.My problem is with RDP. When we re...
Snap! -- Metaverse, CISA Warnings, Solar Eclipse, Cars, Drugs, NASA Studies UFOs
Spiceworks Originals
Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: Back on October 24, 1922, Werner Buchholz was born. He is the guy that invented the term "byte." (Read more HERE.) Bonus Flashback: Back on October 24, 1946, a V-2 rocke...
Service accounts...
Windows
Hi all,While completing a cyber insurance document, I have come across the following question...How many service accounts with domain administrator privileges are in your IT environment? “Service accounts” are non-human privileged accounts used to execute...

Say you have requirement to compare two tables. You have two tables in same database or server that you wish to compare, and check if any changes in the column values or see if any row is missing in either of tables.

Below are some of the methods you can use to compare two tables in SQL.

Compare Two Tables using UNION ALL

UNION allows you to compare data from two similar tables or data sets. It also handles the NULL values to other NULL values which JOIN or WHERE clause doesn’t handle. It allows quickly checking what are the data missing or changed in either table.

Select * from ( 
Select Id_pk, col1, col2...,coln from table1, ‘Old_table’ 
Union all 
Select Id_pk, col1, col2...,coln from table2, 'New_tbale' 
) cmpr order by Id_pk;

The above query returns the all rows from both tables as old and new. You can quickly verify the differences between two tables.

Related reading: Steps to Optimize SQL Query Performance

Compare Two Table using MINUS

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.

The only drawback with using UNION and MINUS is that the tables must have the same number of columns and the data types must match.

Compare Two Table using JOIN

This is the easiest but user has to do some additional work to get the correct result. In this approach you can join the two tables on the primary key of the two tables and use case statement to check whether particular column is matching between two tables.

Select case when A.col1 = B.col1 then ‘Match’ else ‘Mismatch’ end as col1_cmpr, 
case when A.col2 = B.col2 then ‘Match’ else ‘Mismatch’ end as col2_cmpr, ….. 
from table1 A
Join table2 B
on (A.id_pk = B.id_pk) ;

The only drawback of using JOIN is, it cannot compare the NULL values, and you should use the NVL on the column that may have null values in it.

Compare Two Table using NOT EXISTS

The other faster method is to use the NOT EXISTS in WHERE clause of the query. This method is faster and performs well on the large volume of data.

Select id_pk, col1, col2,col,…
From table1 A
Where NOT EXISTS
( select 1 from table2 B
Where A.id_pk = B.id_pk
and A.col1 = B.col1
and A.col2 = B.col2
and…
);

In this approach also you have to use the NVL on the columns which contains NULL in it.

Compare Cells From Two Tables – Cell by Cell Validation

You can use the CASE statement to compare records from both tables and check if the cells are matching from both tables.

For example, following example demonstrates the cell by cell validation.

SELECT CASE
         WHEN Nvl(a.id :: VARCHAR, 'NULL' :: VARCHAR) = Nvl (b.id :: VARCHAR,'NULL' :: VARCHAR) THEN
         'Matched'
         ELSE Nvl(a.id :: VARCHAR, 'NULL' :: VARCHAR)
              ||','
              || Nvl(b.id :: VARCHAR, 'NULL' :: VARCHAR)
       END id,
       CASE
         WHEN Nvl(a.name :: VARCHAR, 'NULL' :: VARCHAR) =
              Nvl (b.name :: VARCHAR,'NULL' :: VARCHAR) THEN
         'Matched'
         ELSE Nvl(a.name :: VARCHAR, 'NULL' :: VARCHAR)
              ||','
              || Nvl (b.name :: VARCHAR, 'NULL' :: VARCHAR)
       END name,
       CASE
         WHEN Nvl(a.addr :: VARCHAR, 'NULL' :: VARCHAR) =
              Nvl (b.addr :: VARCHAR,'NULL' :: VARCHAR) THEN
         'Matched'
         ELSE Nvl(a.addr :: VARCHAR, 'NULL' :: VARCHAR)
              ||','
              || Nvl (b.addr :: VARCHAR, 'NULL' :: VARCHAR)
       END addr
FROM   test_tab1 a
       full outer join test_tab2 b
                    ON Nvl(a.id :: VARCHAR, 'NULL' :: VARCHAR) =
                       Nvl (b.id :: VARCHAR, 'NULL' :: VARCHAR) ;

+---------+----------+------------+
| ID      | NAME     | ADDR       |
|---------+----------+------------|
| Matched | Matched  | Matched    |
| Matched | Matched  | Matched    |
| Matched | Matched  | adr3,adr-  |
| Matched | ddd,dd   | Matched    |
| Matched | eee,NULL | adr5,NULL  |
| NULL,6  | NULL,zzz | NULL,zaddr |
| 7,NULL  | yyy,NULL | yaddr,NULL |
+---------+----------+------------+

You can use LEFT OUTER JOIN or INNER JOIN if you know the table count is matching.

Get Matched and Unmatched Count from Two Tables

You can use full outer join to get matched and unmatched records or count from two tables which has common columns in it.

SELECT
Sum(CASE WHEN t1.file_name IS NOT NULL AND t2.file_n IS NOT NULL THEN 1
ELSE 0 END) AS matched_count,
Sum( CASE WHEN t1.file_name IS NOT NULL AND t2.file_n IS NOT NULL THEN 0
ELSE 1 END) AS un_matched_count
FROM assessment_file_query_mapping AS t1
FULL OUTER JOIN
(
SELECT *
FROM (SELECT DISTINCT regexp_split_to_table(files, e',') AS file_n
FROM job_info) AS a
WHERE length(trim(file_n)) <>0 ) AS t2
ON trim(t1.file_name) = trim(file_n);

We are checking the matched and unmatched file count from two tables. Example uses regular expression to split the comma separated records into table and then join on common file name column.

Netezza Select Random Rows and Example
Netezza Pivot Rows to Column with Example
Netezza Split Delimited Fields into Table Records and Examples
Netezza Update Join Syntax and Examples
Count Records from all Tables in Database and Example
SQL and Hive GROUP BY Alternative-Example

How can I compare two tables in Oracle database?

Comparing Diffs Between Two Oracle Database Schemas.

Select Source and Target..

Select a schema or several schemas (if the tool provides such possibility) for comparison..

Tune comparison process by checking the needed options (an optional step).

View comparison results..

How do you compare values in two tables?

Compare two tables by using joins. To compare two tables by using joins, you create a select query that includes both tables. If there is not already an existing relationship between the tables on the fields that contain the corresponding data, you create a join on the fields that you want to examine for matches.

How do you compare data between two tables in different databases?

Comparing Database Data.

On the SQL menu, point to Data Compare, and then click New Data Comparison. ... .

Identify the source and target databases. ... .

Select the check boxes for the tables and views that you want to compare..

How can I compare two table values in SQL?

Comparing the Results of the Two Queries Let us suppose, we have two tables: table1 and table2. Here, we will use UNION ALL to combine the records based on columns that need to compare. If the values in the columns that need to compare are the same, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1.