插入值语句在SQL数据仓库中只能包含常量文字值或变量引用 [英] Insert values statement can contain only constant literal values or variable references in SQL Data Warehouse

问题描述

考虑此表:

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

我想从一组SELECT语句中将数据插入表中.我的查询的简化版本是:

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), ...);

实际查询可能要复杂得多，并且各个子查询是独立的.不幸的是，此标准SQL语句(在SQL Server上可用)在SQL Data Warehouse上不起作用.引发以下错误:

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?

推荐答案

看来，SQL Data Warehouse的INSERT .. VALUES语句有一些限制，而在INSERT .. SELECT上没有限制.所请求的查询可以重写为:

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;

这篇关于插入值语句在SQL数据仓库中只能包含常量文字值或变量引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！