Comments

• Nimmi_gupta - Select Field - Employee

  June 2014

  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.
  

  0
• Navin_C Vertica Customer ✭

  June 2014

  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
  
  
  
  
  
  
  
  

  0