ZAHIR MOHIDEEN: MERGE

When we want to synchronize the data between same or multiple databases , oracle 11g provides new package called DBMS_COMPARISON. This is a procedural logic.

If this is a straightforward operation , please use MERGE statement.

Let us look at an example.

1. Let us create an user in the source database and create a table called emp from scott schema.

SQL> grant connect , dba to testuser identified by tesTusr ;

Grant succeeded.

SQL> create table emp as select * from scott.emp;
Table created.

SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.

In the destinaton database , let me create an user called testuser.
And also create a database link to the source database.

SQL> grant connect , dba to testuser identified by tesTusr ;
Grant succeeded.

C:\Users\mohideen>sqlplus testuser/tesTusr

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 22 17:14:31 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> create database link

2 remotedb_link connect to srcuser

3 identified by srcusr

4 using 'remotedb';

Database link created.

SQL> select * from dual@remotedb_link;

SQL> create table emp as select * from srcuser.emp@remotedb_link where 1= 2;

Table created.

OK... We are all set . Let us sync it...

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

DECLARE

ERROR at line 1:

ORA-23626: No eligible index on table TESTUSER.EMP

ORA-06512: at "SYS.DBMS_COMPARISON", line 4793

ORA-06512: at "SYS.DBMS_COMPARISON", line 448

ORA-06512: at line 8

OOPS ... We need to add an index . Oracle compares the data by index . ie , The data between the source and the destination objects should be uniquely identifiable.

SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

Differences were found.

Scan ID: 1

Local Rows Merged:14

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.

Yes . We did it . The data has been synchronized. Oracle found there was a difference between the source and the destination objects . Since , we said synchorize the data if there were differences , it did it .

As you can see in the previous example ,

1. first , we have to create an comparison object ( line 8 )

2. second , we have to compare the objects ( line 15)

3. Based on the output of step 2, we sync ( converge) the data .. We can either say which site wins ( either REMOTE or LOCAL) .

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

DECLARE

ERROR at line 1:

ORA-23627: Comparison object "TESTUSER"."COMP_EMP" already existed.

ORA-06512: at "SYS.DBMS_COMPARISON", line 4793

ORA-06512: at "SYS.DBMS_COMPARISON", line 448

ORA-06512: at line 8

Errr.. Let us delete the comparison object and try again

SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

PL/SQL procedure successfully completed.

SQL>

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

No differences were found.

Scan ID: 4

Local Rows Merged:0

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.

SQL> delete from emp where empno = 7934;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

PL/SQL procedure successfully completed.

SQL> DECLARE

2 scan_info dbms_comparison.comparison_type;

3 scan_id INTEGER;

4 consistent BOOLEAN;

5 BEGIN

7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');

8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',

9 schema_name => 'testuser',

10 object_name => 'emp',

11 dblink_name => 'remotedb_link',

12 remote_schema_name => 'srcuser',

13 remote_object_name => 'emp');

15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',

16 scan_info => scan_info);

18 IF consistent = TRUE THEN

19 dbms_output.put_line('No differences were found.');

20 ELSE

21 dbms_output.put_line('Differences were found.');

22 END IF;

24 dbms_comparison.converge(comparison_name => 'comp_emp',

25 scan_id => scan_info.scan_id,

26 scan_info => scan_info,

27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);

30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);

31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);

32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);

33 dbms_output.put_line('Remote Rows Deleted:' ||

34 scan_info.rmt_rows_deleted);

35 END;

36 /

Differences were found.

Scan ID: 5

Local Rows Merged:1

Remote Rows Merged:0

Local Rows Deleted:0

Remote Rows Deleted:0

PL/SQL procedure successfully completed.

In the current releases of Oracle and MS SQL server ,we can use MERGE to ease the ETL process .

The implementation of MERGE statement in SQL Server 2008 seems little more advanced than Oracle's implementation.

Here is our use case.

a) If the record in the source table does not exist in the target table , then add the record.
b) If the record in the source table matches with the record in the target table and there is another condition ( in our example , col3 in target table is 'N' ) ,then update the target record.
c) If the record in the target table does not exists in the source table , then delete it from the target table.

In Oracle's implementation of MERGE , the first two conditions can be easily implemented.
Here's where SQL Server's implementation of MERGE comes handy for the third condition .

Let us create tables ( t_source - for source table and t_dest - for destination table ) .

1> CREATE TABLE t_source
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NOT NULL ,
5>     col3 CHAR(1) NOT NULL
6>   ) ;
7> go

1> CREATE TABLE t_dest
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NULL ,
5>     col3 CHAR(1) NULL
6>   ) ;
7> go

1> insert into t_source values ( 1 , 'Zahir Mohideen' , 'Y') ;
2> insert into t_source values ( 2 , 'Zahir Farook' , 'N') ;
3> insert into t_source values ( 3 , 'Mohideen Farook' , 'N') ;
4> go
(1 rows affected)

1> insert into t_dest values ( 2 , 'Zahir Farook' , 'N') ;
2> insert into t_dest values ( 3 , 'Mohideen Z Farook' , 'Y') ;
3> insert into t_dest values ( 4 , 'Kilakarai' , 'N') ;
4> go
(1 rows affected)

Here are the contents of the tables.

1> select * from t_source
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Farook N

(3 rows affected)

1> select * from t_dest;
2> go
col1 col2 col3
---- ---- ----
2 Zahir Farook N
3 Mohideen Z Farook Y
4 Kilakarai N

(3 rows affected)

Here is our implentaion of MERGE statement .

1> Merge t_dest d USING
2> ( SELECT col1 scol1 , col2 scol2 , col3 scol3 FROM t_source
3> ) s ON ( s.scol1 = d.col1 )
4> WHEN NOT matched THEN
5>   INSERT
6>     (
7>       col1 ,
8>       col2 ,
9>       col3
10>     )
11>     VALUES
12>     (
13>       s.scol1 ,
14>       s.scol2 ,
15>       s.scol3
16>     )
17> WHEN matched AND col3 = 'N' THEN
18>   UPDATE SET col2 = s.scol2 , col3 = s.scol3
19> WHEN NOT matched BY source THEN
20>   DELETE ;
21> go

(3 rows affected)

The code in line # 4 is related to first condition
The code in line # 17 is related to second condition
The code in line # 19 is related to third condition

Here is the result set after running MERGE statement.

1> select * from t_dest
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Z Farook Y

(3 rows affected)

Comments Welcome.

ZAHIR MOHIDEEN

Monday, June 16, 2014

Data comparison

Tuesday, November 29, 2011

MERGE statement in SQL Server