Скачать с ютуб 5. How to Get the Number of Rows Affected by DML Commands| Snowflake Scripting| VCKLY Tech | DEMO в хорошем качестве

5. How to Get the Number of Rows Affected by DML Commands| Snowflake Scripting| VCKLY Tech | DEMO

In this video, I am going to show you How to Get the Number of Rows Affected by DML Commands use database snowflake_scripting; use schema main; use warehouse compute_wh; create or replace table my_table(col1 number); -- One insert stmt execute immediate $$ begin -- Insert 4 rows into a table. insert into my_table values (1), (2), (3),(4); -- SQLROWCOUNT is not affected by statements -- that are not DML statements (e.g. SELECT statements). select * from my_table; -- Returns the number of rows affected by -- the last DML statement (the INSERT statement). return sqlrowcount; end; $$; select * from my_table; truncate table my_table; -- 2 insert stmts execute immediate $$ begin -- Insert 4 rows into a table. insert into my_table values (1), (2), (3),(4); -- Insert 2 rows into a table. insert into my_table values (5), (6); -- Returns the number of rows affected by -- the last DML statement (the INSERT statement). return sqlrowcount; end; $$; select * from my_table; --update stmt with sqlfound execute immediate $$ begin -- Update the rows in a table that have values less than 3. update my_table set col1 = 4 where col1 lessthan 3; -- SQLFOUND and SQLNOTFOUND are not affected by statements -- that are not DML statements (e.g. SELECT statements). select * from my_table; -- SQLFOUND returns 'true' if the last DML statement -- (the UPDATE statement) affected one or more rows. if (sqlfound = true) then return 'Updated ' || sqlrowcount || ' rows.'; -- SQLNOTFOUND returns 'true' if the last DML statement -- (the UPDATE statement) affected zero rows. elseif (sqlnotfound = true) then return 'No rows updated.'; else return 'SQLFOUND and SQLNOTFOUND are not true.'; end if; end; $$; --Update stmt with sqlnotfound execute immediate $$ begin update my_table set col1 = 4 where 1 = 2; -- SQLFOUND and SQLNOTFOUND are not affected by statements -- that are not DML statements (e.g. SELECT statements). select * from my_table; -- SQLFOUND returns 'true' if the last DML statement -- (the UPDATE statement) affected one or more rows. if (sqlfound = true) then return 'Updated ' || sqlrowcount || ' rows.'; -- SQLNOTFOUND returns 'true' if the last DML statement -- (the UPDATE statement) affected zero rows. elseif (sqlnotfound = true) then return 'No rows updated.'; else return 'SQLFOUND and SQLNOTFOUND are not true.'; end if; end; $$; update my_table set col1 = 4 where 1 = 2; -- Delete stmt execute immediate $$ begin delete from my_table; return sqlrowcount; end; $$; -----Merge with update only create or replace table target_table (id integer, description varchar); insert into target_table (id, description) values (10, 'To be updated (this is the old value)') ; create or replace table source_table (id integer, description varchar); insert into source_table (id, description) values (10, 'To be updated (this is the new value)') ; execute immediate $$ begin merge into target_table using source_table on target_table.id = source_table.id when matched then update set target_table.description = source_table.description; return sqlrowcount; end; $$; select * from target_table; -----Merge with insert and update create or replace table target_table (id integer, description varchar); insert into target_table (id, description) values (10, 'To be updated (this is the old value)') ; create or replace table source_table (id integer, description varchar); insert into source_table (id, description) values (10, 'To be updated (this is the new value)') ; insert into source_table (id, description) values (11, 'To be inserted (this is the old value)') ; execute immediate $$ begin merge into target_table using source_table on target_table.id = source_table.id when matched then update set target_table.description = source_table.description when not matched then insert (id, description) values (source_table.id, source_table.description); return sqlrowcount; end;$$; -- Multi insert create or replace table t1( c1 number, c2 number, c3 number); create or replace table t2( c1 number, c2 number, c3 number); create or replace table src(n1 number, n2 number , n3 number); insert into src values (1, 2,3); insert into src values (4, 5,6); execute immediate $$ begin insert all into t1 into t2 (c1, c2, c3) select n1, n2, n3 from src; return sqlrowcount; end; $$; select * from t1 union all select * from t2; -- Truncate stmt select * from my_table; insert into my_table values (1); execute immediate $$ begin -- Truncate a table. truncate table my_table; return sqlrowcount; end; $$; select * from my_table; #snowflake #vcklytech #snowflakescripting#snowpark#datacloud