Insert differently with Snowflake and SQL Server

One of the things that I’ve gotten used to with Snowflake is that it’s just different. Well, today, I’ve got some goodness for y’all – and in a place that you really wouldn’t expect – the INSERT statement.

For most of your everyday use, it works exactly as you’d expect. As some would say, it does what it says on the tin – INSERT data.

Yep! We’re rocking it. INSERTing data here. Nothing really fancy. If you really want to learn about how to INSERT data, you can view it here as I’ve discussed before.

Now, let’s say that we want to delete all of the data from the table and then write everything to it. In SQL Server, we have to code the following:

TRUNCATE TABLE public.Testing;
INSERT INTO public.Testing 
SELECT 'Sherpa', 'Data', 30, '1990-07-16', NULL; 

In Snowflake, we can do something a little different.

INSERT OVERWRITE INTO TESTING
(FirstName, LastName, Age, DOB, DOD)
SELECT 'Sherpa', 'Data', 30, '1990-07-16', NULL

Notice the OVERWRITE command after INSERT. This tells Snowflake to get rid of the data that was in the table and then INSERT what we need. Is it a great thing for programming to allow this? Well, that I’ll leave to the developers out there and the best practices.

For this next one, I created another table called Testing1. Yes, I know a real imaginative naming scheme. Let’s do something crazy now.

So, what did we do here? I inserted data into 2 different tables with the same statement! If you have data going into 2 (or more) different tables, you can do it all in one query. Now whether it should be done, I’ll leave that up to the reader.

As shown above, I can also say if data goes to one or another. This actually puts all of the data (as shown by the ALL keyword on line 36 above) out to any table that fits the criteria in the WHEN statements.

Now, if we would like, we can use the FIRST keyword instead of ALL, and only the first group that the data falls into will get the data.

Notice that the FIRST keyword is the only part I changed. The only row with an age greater than 95 is pushed to the Testing table. The other rows are pushed to the Testing1 table.

With that, my friends, I’m going to call it a night. Lots of ways to INSERT data that are different from SQL Server. Hopefully, you’ve learned something and will come back to join me next time. Until then, remember to play in Snowflake and learn what you can!