Proof that Less can be More
Snowflake Data Warehouse release 2.11 (September 7, 2017) included the addition of an OVERWRITE parameter for the INSERT command. INSERT OVERWRITE effectively consolidates two commands, TRUNCATE TABLE and INSERT, into one. INSERT OVERWRITE also deletes the file load history and retains access control privileges on the target table; both of which are not insignificant details.
Give it a try by creating a table and populating with two records:
create table test_insert_overwrite(
id int
,first_name varchar2(250)
,last_name varchar2(250))
;
insert into test_insert_overwrite values(1,'Wayne','Campbell'),(2,'Garth','Algar');
Verify that the two records are present:
select * from test_insert_overwrite;
+----+------------+-----------+
| ID | FIRST_NAME | LAST_NAME |
+====+============+===========+
| 1 | Wayne | Campbell |
+----+------------+-----------+
| 2 | Garth | Algar |
+----+------------+-----------+
2 Row(s) produced. Time Elapsed: 1.038s
Now add the OVERWRITE option to the INSERT command:
insert overwrite into test_insert_overwrite values (3,'Noah','Vanderhoff');
Confirm the original two records have been removed and there is now one record in the table:
select * from test_insert_overwrite;
+----+------------+------------+
| ID | FIRST_NAME | LAST_NAME |
+====+============+============+
| 3 | Noah | Vanderhoff |
+----+------------+------------+
1 Row(s) produced. Time Elapsed: 1.930s
The Query Profile for the INSERT OVERWRITE command used above shows a two-step process. First, a TRUNCATE command followed by the INSERT.
While there are multiple methods to achieve the desired result, using one statement to perform a “full load” is often the most efficient for many use cases.
