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

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

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

CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP TABLE IF EXISTS NEWTABLE;
        CREATE OR REPLACE TABLE NEWTABLE (
            COL1 INT,
            COL2 VARCHAR
        );
            BEGIN TRANSACTION
-- ** MSC-WARNING - MSCEWI1101 - COMMENTED OUT TRANSACTION LABEL NAME BECAUSE IS NOT APPLICABLE IN SNOWFLAKE **
--                              LabelA
                                    ;
            INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
        INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
            COMMIT;
    END;
$$;

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.