2 TABLE MERGE SQL

 EX NO:6

Write a PL/SQL block of code using parameterized Cursor, that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table then that data should be skipped. 

Meaning of PL/SQL Block

A PL/SQL Block is a structured group of statements written in PL/SQL (Procedural Language/Structured Query Language) that are executed together as a single unit.

It allows you to combine:

• SQL statements (like SELECT, INSERT, UPDATE, DELETE)

• Procedural statements (like loops, conditions, variables, exception handling)

Meaning of the Given PL/SQL Program

This PL/SQL block is written in Oracle Database.

Purpose of the Program

The program merges data from table N_RollCall (New Roll Call) into O_RollCall (Old Roll Call).

1, If a record from N_RollCall already exists in O_RollCall, it is skipped.
2, If it does not exist, it is inserted into O_RollCall.

STEP1: CREATE TABLE      N_RollCall:

CREATE TABLE N_RollCall (

    id INT,

    name VARCHAR(100),

    roll_date DATE

);

STEP2: INSERT DATA  in            N_RollCall Table:

INSERT INTO N_RollCall VALUES (1, 'RAJ', '2015-01-22');

INSERT INTO N_RollCall VALUES (2, 'KING', '2016-04-22');

INSERT INTO N_RollCall VALUES (3, 'RUTH', '2017-05-22');

INSERT INTO N_RollCall VALUES (4, 'BABU', '2017-05-22');

STEP3: CREATE TABLE      O_RollCall:

CREATE TABLE O_RollCall (

    id INT,

    name VARCHAR(100),

    roll_date DATE

);

STEP4: INSERT DATA  in            O_RollCall Table:

INSERT INTO O_RollCall VALUES (1, 'BABU', '2015-01-22');

INSERT INTO O_RollCall VALUES (2, 'KRISH', '2016-04-22');

INSERT INTO O_RollCall VALUES (3, 'JONA', '2017-05-22');

Step 5: Merge N_RollCall into O_RollCall while skipping duplicate names

INSERT INTO O_RollCall (id, name, roll_date)

SELECT n.id, n.name, n.roll_date

FROM N_RollCall n

WHERE NOT EXISTS (

    SELECT 1

    FROM O_RollCall o

    WHERE o.name = n.name

);

Step 6: Optional – Remove Existing Duplicate Names First

DELETE o1

FROM O_RollCall o1

JOIN O_RollCall o2

ON o1.name = o2.name

AND o1.id > o2.id;

Step 7: Optional – Add UNIQUE Constraint to Prevent Future Duplicates

ALTER TABLE O_RollCall

ADD CONSTRAINT unique_name UNIQUE (name);

Step 8:View the Final Table

SELECT * FROM O_RollCall;