insert on synapse DW in ssms

Question

simple insert code but i keep getting syntax errors the values lines have a value for each column in the table, it only has 3 columns, i've tried removing the comma, tried using semi colon tried nothing after closing parent, tried explicitly stating column name before values nothing works on this simple bit of code

Plotkin

Answer 1

Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse) does not support the INSERT ... VALUES clause for more than a single row.

Simply convert these into a SELECT with UNION ALL:

INSERT INTO dbo.countryCurrency 
          SELECT 'Afganistan', 'Afghani', 'AFN'
UNION ALL SELECT 'Aland Islands', 'Euro', 'EUR'
UNION ALL SELECT 'Albania', 'Lek', 'ALL'
UNION ALL SELECT 'Algeria', 'Algerian Dinar', 'DZD'
UNION ALL SELECT 'American Samoa', 'US Dollar', 'USD'
-- ...

Sampler

Answer 2

The SQL Server table value constructor is not supported in Azure Synapse, see https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql.

Shutt