[netghost]

So I've never come across INSERT table SELECT values UNION ALL SELECT values.

Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?

[RmDen]

No reason, I do use Row Value Constructor/Table Value Constructor, these were introduced in SQL Server 2008, while CTEs were introduced in SQL Server 2005, I think when I created this example initially SQL Server 2008 was just released so this would not have worked for a lot of people on 2005

[AlisdairO]

As I very vaguely recall, not all DB systems have/had support for multiple VALUES statements.

[politician]

You'll find INSERT SELECT UNION ALL interesting when you want to insert rows from multiple table sources. For example, when refactoring a two tables into a single table or loading data into a temporary table (BCP) before copying it into a target table.

[a_m0d]

SQL Server has a limitation of 1000 rows maximum allowed in a VALUES clause, so sometimes a SELECT ... UNION ALL is required instead (this limitation probably doesn't exist in Postgres, though).

[RmDen]

One advantage of this syntax is that you can just run the select part, look at the data to make sure it is correct and then finally run the whole statement