[ycom13__]

Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this

  ;WITH cte AS(SELECT   
        ROW_NUMBER() OVER(PARTITION BY SomeVal   
                  ORDER BY SomeDate DESC) AS row,  
                * FROM SomeTable)

  UPDATE cte  
  SET id =  CASE WHEN row = 1 THEN 1 ELSE 0 END

Here is the DDL and DML in case you want to play around with this

  CREATE TABLE SomeTable(id int,SomeVal char(1),SomeDate date)
  INSERT SomeTable 
  SELECT null,'A','20110101'
  UNION ALL
  SELECT null,'A','20100101'
  UNION ALL
  SELECT null,'A','20090101'
  UNION ALL
  SELECT null,'B','20110101'
  UNION ALL
  SELECT null,'B','20100101'
  UNION ALL
  SELECT null,'C','20110101'
  UNION ALL
  SELECT null,'C','20100101'
  UNION ALL
  SELECT null,'C','20090101'

[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