BEGIN and COMMIT Transaction
Translation reference to convert Transact-SQL 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.
Transact-SQL
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
Snowflake SQL
CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
DROP TABLE IF EXISTS NEWTABLE;
CREATE OR REPLACE TABLE NEWTABLE (
COL1 INT,
COL2 VARCHAR
);
BEGIN TRANSACTION;
INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
COMMIT;
END;
$$;
Known Issues
Nested transactions are not supported in Snowflake. Review the following documentation for more information: https://docs.snowflake.com/en/sql-reference/transactions
Related EWIs
SSC-EWI-0101: Commented out transaction label name because is not applicable in Snowflake.
Last updated