3 ms·
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,
by netghost 11y ago
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 11y agoNo 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 11y agoAs I very vaguely recall, not all DB systems have/had support for multiple VALUES statements.
- politician 11y agoYou'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.
- RmDen 11y agoOne 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
- a_m0d 11y agoSQL 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).