Insert values statement can contain only constant literal values or variable references in SQL Data Warehouse

Question

Consider this table:

CREATE TABLE t (i int, j int, ...);

I want to insert data into a table from a set of SELECT statements. The simplified version of my query is:

INSERT INTO t VALUES ((SELECT 1), (SELECT 2), ...);

The real query can be much more complex, and the individual subqueries independent. Unfortunately, this standard SQL statement (which works on SQL Server) doesn't work on SQL Data Warehouse. The following error is raised:

Failed to execute query. Error: Insert values statement can contain only constant literal values or variable references.

Is there a way to work around this?

Solution 1

It appears that there are a few limitations on the INSERT .. VALUES statement of SQL Data Warehouse, but none on INSERT .. SELECT. The requested query can be rewritten to:

INSERT INTO t SELECT (SELECT 1), (SELECT 2);

This workaround is also useful when inserting multiple rows:

-- Doesn't work:
INSERT INTO t VALUES ((SELECT 1), 2), ((SELECT 2), 3), ...;

-- Works:
INSERT INTO t SELECT (SELECT 1), 2 UNION ALL SELECT (SELECT 2), 3;

Solution 2

You can also just run a CREATE TABLE AS SELECT (CTAS) statement. This gives you the full syntax support in the SELECT statement and control of the table shape (distribution type, index type) in the statement. A CTAS statement is fully parallalized.