Azure Synapse INSERT with VALUES Limitations and Alternative

Azure Synapse Analytics data warehouse is based on TSQL. But, not all TSQL features are available in Azure Synapse dedicated SQL pool. The feature such as using an expression or UDF in VALUES clause of INSERT INTO table statement is not supported. In this article, we will check Azure Synapse INSERT with VALUES limitations and alternative methods.

Azure Synapse INSERT with VALUES Limitations

The VALUES clause Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The value list must be enclosed in parentheses.

If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.

However, in Azure Synapse Analytics, insert values can only be constant literal values or variable references. To insert a non-literal, set a variable to non-constant value and insert the variable.

Following insert statement will work on SQL Server but same will fail on Azure Synapse Analytics.

insert into students (firstname, lastName, age, doj) values (substring('Mark L', 1,4),'L', getdate());

You will end up getting following error message.

Insert values statement can contain only constant literal values or variable references.

Azure Synapse INSERT with VALUES Alternative Methods

Following are the couple of alternative that you can use as per your requirements

INSERT INTO Table using SELECT Clause
Use Variable inside VALUES Clause

Now, Let us check these two methods in brief.

INSERT INTO Table using SELECT Clause

One of the common alternative is to use INSERT INTO table using SELECT clause.

Following example, use SELECT statement to insert records into table.

INSERT INTO students
SELECT substring('Mark L', 1,4),'L', 50, cast(getdate() as date);

You can also specify the table column list while using SELECT clause.

For example,

INSERT INTO students(firstname, lastName, age, doj)
SELECT substring('Mark L', 1,4),'L', 50, cast(getdate() as date);

Use Variable inside VALUES Clause

This is another common method sometime useful when you need to use the expression, UDF or built-in functions on the values before inserting into tables.

Following example use local variables in the VALUES clause.

DECLARE @fname varchar(50) = substring('Mark L', 1,4)
DECLARE @ddate date = cast(getdate() as date)

insert into students (firstname, lastName, age, doj) values (@fname,'L',50, @ddate);