BEGIN and COMMIT Transaction
Translation reference to convert SQL Server BEGIN and COMMIT transaction to Snowflake SQL
Last updated
Translation reference to convert SQL Server BEGIN and COMMIT transaction to Snowflake SQL
Last updated
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.
The following examples detail the BEGIN and COMMIT transaction statements.
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
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;
$$;
Nested transactions are not supported in Snowflake. Review the following documentation for more information: https://docs.snowflake.com/en/sql-reference/transactions
SSC-EWI-0101: Commented out transaction label name because is not applicable in Snowflake.