BEGIN and COMMIT Transaction

Translation reference to convert SQL Server BEGIN and COMMIT transaction to Snowflake SQL

Description

Snowflake SQL, a transaction can be started explicitly by executing a BEGIN statement. Snowflake supports the synonyms BEGIN WORK and BEGIN TRANSACTION. Snowflake recommends using BEGIN TRANSACTION.

A transaction can be ended explicitly by executing COMMIT. Read more about Snowflake Transactions here.

Sample Source Patterns

The following examples detail the BEGIN and COMMIT transaction statements.

SQL Server

Begin/Commit transaction

CREATE PROCEDURE TestTransaction
AS
BEGIN
    DROP TABLE IF EXISTS NEWTABLE;
    CREATE TABLE NEWTABLE(COL1 INT, COL2 VARCHAR);
      BEGIN TRANSACTION;
         INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
         INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
      COMMIT TRANSACTION;
END

Begin/Commit transaction with label

Snowflake SQL

Begin/Commit transaction

CREATE OR REPLACE PROCEDURE PUBLIC.TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
     DROP TABLE IF EXISTS PUBLIC.NEWTABLE;
     CREATE OR REPLACE TABLE PUBLIC.NEWTABLE (
     COL1 INT,
     COL2 VARCHAR);
     BEGIN TRANSACTION;
     INSERT INTO PUBLIC.NEWTABLE VALUES (1, 'MICHAEL');
     INSERT INTO PUBLIC.NEWTABLE VALUES(2, 'JACKSON');
  COMMIT;
END;
$$;

Begin/Commit transaction with label

Known Issues

1. Nested transactions are not supported in Snowflake. Review the following documentation for more information: https://docs.snowflake.com/en/sql-reference/transactions

Related EWIs

MSCEWI1101: Commented out transaction label name because is not applicable in Snowflake.