when does data gets committed during MERGE??

sanikommu

I am using MERGE statement to insert and update data, my question is does vertica commits data at the end of the operations?

Nimmi_gupta

I believe yes!!

Because in document it's not mentioned that you need to issue a COMMIT or ROLLBACK command to finish the transaction like INSERT has.

Navin_C

Hi,

Merge is another DML command just like INSERT/UPDATE/DELETE, so it should be manually committed unless it is not a part of BEGIN....END transaction.

nnani=> select * from navin.Test1; id | name
----+------
  2 | sbc
  3 | cbc
  1 | abc
  4 | dbc
  5 | ebc
(5 rows)
nnani=> select * from navin.Test2;
 id | name
----+------
  1 | abc
  6 | gbc
(2 rows)

Now we will run a merge command to update the Test2 table with new records.

nnani=> MERGE INTO navin.Test2 T2 USING navin.Test1 T1nnani-> ON T2.ID = T1.ID
nnani-> WHEN MATCHED THEN
nnani-> UPDATE SET
nnani-> ID = T1.ID,
nnani-> NAME = T1.NAME
nnani-> WHEN NOT MATCHED THEN
nnani-> INSERT
nnani-> (ID, NAME ) VALUES (T1.ID, T1.NAME);
 OUTPUT
--------
      5
(1 row)

Lets check if the records are autocommited. Opening a new session and checking .

nnani=> select * from navin.Test2;
 id | name
----+------
  1 | abc
  6 | gbc
(2 rows)
nnani=>

The records are not committed, unless you execute a explicit COMMIT statement

nnani=> COMMIT;
COMMIT
nnani=> select * from navin.Test2;
 id | name
----+------
  5 | ebc
  4 | dbc
  1 | abc
  6 | gbc
  3 | cbc
  2 | sbc
(6 rows)
nnani=>

Now the records are committed.

Hope this helps