Pushing data into one table from another in Snowflake

Most of us have run across the scenario where we have data in one table, but we need the exact same data in another table in another database.

In SQL Server, I’d do it this way:

INSERT INTO TableB (FirstName, LastName) 
SELECT FirstName, LastName FROM TableA;

Yes, pretty simple. Basically, we’re grabbing all of the data from TableA and inserting it into TableB.

A few small problems though.

1. The more data we have in TableA to send to TableB, the longer this process will take.
2. If you need TableB to have the “special” permissions that TableA has that are needed in TableB, then you will have to set those up manually.

In Snowflake, on the other hand, we have this great idea called “zero-copy cloning.”

CREATE TABLE TableB CLONE TableA;

In this coding scenario, I am copying everything from TableA and pushing it into a new table called TableB in the same database and schema.

If you check the size of the database before and after you clone a table, it will be the same size – no matter the size of TableA. This is because, at this point in time, TableB exists only as a “pointer” to the data that constitutes TableA. It is not until something changes in one of the tables – say adding a row to TableA, that it stops being a “pointer” and is artificially constituted.

Now, if I want to make sure that I have all of the permissions copied from TableA to TableB, then I do a slightly different command:

CREATE TABLE TableB CLONE TableA COPY GRANTS;

And now, you have all* of the permissions that you have in TableA in TableB.

* Except for ownership. That one doesn’t copy over, so be aware!

With that, I hope that you’ve learned a few points of fun with Cloning in Snowflake. And it’s always great to learn some new ways to get data into tables with Snowflake. Next time, we’ll learn some more great uses for cloning. So until then my friends!