CODEWITHSUNDEEP
databaseoracle-databaseplsql
jakonda
jakonda

Reputation: 57

Stored Procedure to check matching records in two tables of two different databases PL/SQL

I have a task to create a stored procedure in Oracle DB. Given two different databases DB1 with student_lookup table and DB2 with student_master table . The SP needs to check if DB2.student_master's record exists in DB1.student_lookup table.

  1. If the record exists in DB1 then don't anything
  2. If the record doesn't exists in DB1 then add from DB2
  3. If the record is in DB1 but not DB2 then update that record and set partition_key column to 1.

Any help will be appreciated. I am completely new to Oracle DBA.

Upvotes: 1

Views: 261

Answers (1)

Rustam Pulatov
Rustam Pulatov

Reputation: 665

If it's two users using:

MERGE INTO db1.student_lookup a 
USING 
(select * from db2.student_mater) b 
ON (
a.id = b.id 
AND <others join column>
)
WHEN MATCHED THEN UPDATE SET a.partition_key = 1
WHEN NOT MATCHED THEN INSERT (<a.column>)
VALUES (<b.column>)

If it's two db:

CREATE DATABASE LINK DBLINK_DB1_DB2
  CONNECT TO DB2 IDENTIFIED BY <ENTER USER PASSWORD HERE>
  USING '<FROM tnsnames>'


MERGE INTO db1.student_lookup a 
USING 
(select * from "student_mater"@"DBLINK_DB1_DB2") b 
ON (
a.id = b.id 
AND <others join column>
)
WHEN MATCHED THEN UPDATE SET a.partition_key = 1
WHEN NOT MATCHED THEN INSERT (<a.column>)
VALUES (<b.column>)

If you neen SP simple megre into your SP.

Upvotes: 1

Related Questions