How to Work Around the "Insert Values Statement Can Contain Only Constant Literal Values or Variable References" Error in SQL Data Warehouse?

SQL is a powerful language used to manage and manipulate data in relational databases. However, different SQL implementations may have unique constraints and limitations. One such limitation in SQL Data Warehouse is the inability to use subqueries within an INSERT INTO statement. This blog post will explore this issue and provide a workaround.

Introduction

Consider a scenario where you want to insert data into a table using a set of SELECT statements. While this is a standard SQL statement that works on SQL Server, it may not work on SQL Data Warehouse. An example of such a query is:

CREATE TABLE t (i int, j int, ...);
INSERT INTO t VALUES ((SELECT 1), (SELECT 2), ...);

1 2	CREATE TABLE t (i int, j int, ...); INSERT INTO t VALUES ((SELECT 1), (SELECT 2), ...);

Executing this query in SQL Data Warehouse results in the following error:

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

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

So, what does this error mean, and how can you work around it? Let’s dive into the concepts and find a solution.

Concepts Used

1. SQL Data Warehouse

SQL Data Warehouse is a cloud-based, enterprise-class data warehouse solution that allows for massive parallel processing. It’s designed to handle large volumes of data and complex queries but has specific constraints, such as the one mentioned above.

2. INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table. It can take constant values, variable references, or even subqueries in some SQL implementations.

3. Subqueries

A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data retrieved.

Step-by-Step Guide to the Workaround

Here’s a step-by-step guide to working around the limitation in SQL Data Warehouse:

Create a Temporary Table: Create a temporary table to store the results of the subqueries.
Use SELECT INTO: Use the SELECT INTO statement to insert the results of the subqueries into the temporary table.
Insert from Temporary Table: Use a standard INSERT INTO statement to insert the data from the temporary table into the target table.
Drop the Temporary Table: Optionally, drop the temporary table once the data has been inserted.

Here’s an example:

CREATE TABLE temp_table AS SELECT 1 AS i, 2 AS j;
INSERT INTO t SELECT * FROM temp_table;
DROP TABLE temp_table;

CREATE TABLE temp_table AS SELECT 1 AS i, 2 AS j;

INSERT INTO t SELECT * FROM temp_table;

DROP TABLE temp_table;

Conclusion

The limitation in SQL Data Warehouse that prevents the use of subqueries within an INSERT INTO statement can be frustrating. However, by understanding the underlying concepts and following the step-by-step guide provided, you can work around this limitation and achieve the desired results.

Remember, SQL implementations may vary, and understanding the specific constraints of the system you’re working with is key to writing effective and error-free code.