如何在 SQL Server 并行数据仓库表中插入多行 [英] How to insert multiple rows into SQL Server Parallel Data Warehouse table

问题描述

我在使用 SQL Server 2012 服务器的 PDW AU5.我有一个空的复制表，我正在尝试将数据加载到其中.我只加载 2 条记录.所以，我正在做:

I am on PDW AU5 with SQL Server 2012 servers. I have an empty replicated table that I'm trying to load data into. I'm only loading 2 records. So, I'm doing:

INSERT INTO dbo.some_table
(Col1, Col2, Col3)
VALUES
(1, 'x', 'a'),
(2, 'y', 'b')

从网上的书籍来看，这应该有效.(它适用于 SMP.)但是，PDW 抛出一个错误说明:Parse error at line: 4, column: x: Incorrect syntax near ','.

From the books online, this should work. (It works on SMP.) However, PDW throws an error stating: Parse error at line: 4, column: x: Incorrect syntax near ','.

此错误所指的逗号是第一个元组之后的逗号.我究竟做错了什么?AU5 上不允许通过 INSERT INTO 插入多行吗?

The comma that this error is referring to is the one after the first tuple. What am I doing wrong? Is inserting multiple rows via INSERT INTO not allowed on AU5?

推荐答案

INSERT INTO 的 MSDN 文档指出，与普通数据仓库相比，并行数据仓库和 Azure SQL 数据仓库使用不同的插入语法不幸的是，SQL Server 不支持多个 VALUES 元组:https://msdn.microsoft.com/en-us/library/ms174335.aspx

The documentation on MSDN for INSERT INTO states that Parallel Data Warehous and Azure SQL Data Warehouse uses a different syntax for insertions compared to normal SQL Server which crucially does not support multiple VALUES tuples unfortunately: https://msdn.microsoft.com/en-us/library/ms174335.aspx

-- Azure SQL Data Warehouse and Parallel Data Warehouse
INSERT INTO [ database_name . [ schema_name ] . | schema_name . ] table_name 
    [ ( column_name [ ,...n ] ) ]
    { 
      VALUES ( { NULL | expression } [ ,...n ] )
      | SELECT <select_criteria>
    }
    [ OPTION ( <query_option> [ ,...n ] ) ]
[;]

但是请注意，它确实支持 INSERT INTO [...] SELECT [..] 语法，因此您可以像这样修改它:

However note that it does support INSERT INTO [...] SELECT [..] syntax, so you could hack it like so:

INSERT INTO foo ( x, y, z )
SELECT 1, 'x', 'a' UNION ALL
SELECT 2, 'y', 'b' UNION ALL
SELECT 3, 'z', 'c' UNION ALL
SELECT 4, 'a', 'd' UNION ALL
SELECT 5, 'b', 'e'

(最后一行没有 UNION ALL 表达式)

(The last line doesn't have a UNION ALL expression)

这篇关于如何在 SQL Server 并行数据仓库表中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！