DBMSL 7

CREATE TABLE O_RollCall (RollNo INT, Name varchar(30), Address varchar(50));
INSERT INTO O_RollCall VALUES (1, 'Amit', 'Pune');
INSERT INTO O_RollCall VALUES (2, 'Sahil', 'Nanded');
INSERT INTO O_RollCall VALUES (3, 'Tilak', 'Mumbai');

SELECT * FROM O_RollCall;
-- +--------+-------+---------+
-- | RollNo | Name  | Address |
-- +--------+-------+---------+
-- |      1 | Amit  | Pune    |
-- |      2 | Sahil | Nanded  |
-- |      3 | Tilak | Mumbai  |
-- +--------+-------+---------+

CREATE TABLE N_RollCall (RollNo INT, Name varchar(30), Address varchar(50));

DELIMITER $
CREATE PROCEDURE InsertIntoNew()
BEGIN
  DECLARE RNo INT;
  DECLARE ExitLoop BOOLEAN;
  DECLARE C1 CURSOR FOR SELECT RollNo FROM O_RollCall;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitLooP= TRUE;
  OPEN C1;
  C1_Loop: LOOP 
    FETCH C1 INTO RNo;
    IF NOT EXISTS(SELECT * FROM N_RollCall WHERE RollNo=RNo) THEN
      INSERT INTO N_RollCall SELECT * FROM O_RollCall WHERE RollNo=RNo;
    END IF;
    IF ExitLoop THEN 
      CLOSE C1;
      LEAVE C1_Loop;
    END IF;
  END LOOP C1_Loop;
END $

DELIMITER ;
  
CALL InsertIntoNew();

SELECT * FROM N_RollCall;
-- +--------+-------+---------+
-- | RollNo | Name  | Address |
-- +--------+-------+---------+
-- |      1 | Amit  | Pune    |
-- |      2 | Sahil | Nanded  |
-- |      3 | Tilak | Mumbai  |
-- +--------+-------+---------+

INSERT INTO O_RollCall VALUES (4, 'Ram', 'Latur');

SELECT * FROM O_RollCall;
-- +--------+-------+---------+
-- | RollNo | Name  | Address |
-- +--------+-------+---------+
-- |      1 | Amit  | Pune    |
-- |      2 | Sahil | Nanded  |
-- |      3 | Tilak | Mumbai  |
-- |      4 | Ram   | Latur   |
-- +--------+-------+---------+

SELECT * FROM N_RollCall;
-- +--------+-------+---------+
-- | RollNo | Name  | Address |
-- +--------+-------+---------+
-- |      1 | Amit  | Pune    |
-- |      2 | Sahil | Nanded  |
-- |      3 | Tilak | Mumbai  |
-- +--------+-------+---------+

CALL InsertIntoNew();

SELECT * FROM N_RollCall;
-- +--------+-------+---------+
-- | RollNo | Name  | Address |
-- +--------+-------+---------+
-- |      1 | Amit  | Pune    |
-- |      2 | Sahil | Nanded  |
-- |      3 | Tilak | Mumbai  |
-- |      4 | Ram   | Latur   |
-- +--------+-------+---------+