Insert values statement can contain only constant

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?

Answer 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;

Answer 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.

Answer 3

Strange syntax, but it works. Here is a more complex example:

CREATE TABLE [MDM].[Fact_Management_Curve]
(
 [Scenario_ID] INT NOT NULL,
 [FundingYYYYMM] CHAR(6) NOT NULL,
 [CollectionYYYYMM] CHAR(6) NOT NULL,
 [CorpID] INT NOT NULL,
 [Multipler] FLOAT NOT NULL
)
GO

INSERT INTO [MDM].[Fact_Management_Curve]
SELECT (SELECT 1), 201701, 201701, 21, 0.010170154301011 UNION ALL
SELECT (SELECT 1), 201701, 201702, 21, 0.010170278901234 UNION ALL
SELECT (SELECT 1), 201701, 201703, 21, 0.010170375659900 UNION ALL
SELECT (SELECT 1), 201701, 201704, 21, 0.010170482998344
GO

SELECT * FROM  [MDM].[Fact_Management_Curve]
ORDER BY 1,2,3,4;
Scenario_ID  FundingYYYYMM  CollectionYYYYMM  CorpID  Multipler
1            201701         201701            21      0.010170154301011
1            201701         201702            21      0.010170278901234
1            201701         201703            21      0.0101703756599
1            201701         201704            21      0.010170482998344