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?

Cornaceous

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;

Cornaceous

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.

Hahnert

Sure, but I added the CREATE TABLE statement just for illustration purposes to document the schema. I've changed the question to clarify this. – Cornaceous

Exactly how is creating a new table a good substitution for inserting rows into another one? – Proboscidean

@DanBracuk: The way my question was phrased at first, CTAS was indeed a viable alternative and a good thing to keep in mind in general. – Cornaceous

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

Burr

Answer 4

For your information...

INSERT INTO table_name VALUES Syntax only accepts constant literal values or variable references. Anything like Expression is invalid.

For Example INSERT INTO table_name VALUES (A,B,A+B)

But DECLARE @C INT = A+B INSERT INTO table_name VALUES (A,B,C) is valid one.

Here A+B is like any kind of expressions so it throw the error like "only accepts constant literal values or variable references"

Narcoanalysis