How to insert multiple rows into SQL Server Parallel Data Warehouse table

Question

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')

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 ','.

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?

Siglos

This should work! I have used this pattern many time. – Frontolysis

Answer 1

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 ] ) ]
[;]

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'

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

Tungstic

I'm getting an Incorrect syntax near 'ALL'. error – Angrist

Oh, I got it, last row should go without UNION ALL and with ; in the end – Angrist

@AlexeyStrakh Thank you for the tip, I've amended my answer. – Tungstic

Answer 2

Using INSERT INTO and SELECT: If you have a very large number of rows to insert it can fail with Error:

Msg 102042, Level 16, State 1, Line 1    
The query processor ran out of stack space during query optimization. Please simplify the query.

The only alternative I have found is to insert each row individually as follows:

create table #A (ID integer, CollA varchar(10),  CollB Varchar(50))

Insert into #A values (2176035,'ADM1','DIFFERENT TO OPS1 APP')    
Insert into #A values (5530921,'ADM7','DIFFERENT TO OPS1 APP')    
Insert into #A values (5034949,'ADM7','DIFFERENT TO OPS1 APP')    
Insert into #A values (3780563,'ADM4','DIFFERENT TO OPS1 APP')    
Insert into #A values (5215169,'ADM2','DIFFERENT TO OPS1 APP')

Multipurpose