Feeding few data for some Synapse tables

pmscorca 1,007

Hi,

for a my Synapse Analytics solution I've a dedicated SQL pool and I need to feed some tables with few row data (10-15-20 rows). I've already used the INSERT ... VALUES statement, but repeating this instruction in a complete manner (for SQL Server I can write the INSERT keyword only one time).

Does it exist a more quick/efficient manner to solve a such issue? Thanks

Bhargava-MSFT 31,196 Reputation points Microsoft Employee

2023-11-07T19:41:28.2666667+00:00
Hello pmscorca,

Since you have a few rows of data, you can use insert into statement with multiple row values.

This allows you to insert multiple rows with a single Insert statement.

Reference document: https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16#b-inserting-multiple-rows-of-data

INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923') , (N'Y3', N'Cubic Yards', '20080923');

You can add as many rows as you need, separated by commas. This method is more efficient than using a separate insert statement for each row of data.

I hope this helps! Let me know if you have any other questions.
Bhargava-MSFT 31,196 Reputation points Microsoft Employee

2023-11-08T17:33:37.35+00:00

Hello pmscorca,

I am checking to see if the earlier response is helpful. Please let us know if you still have any further questions.
pmscorca 1,007 Reputation points

2023-11-09T08:36:49.2166667+00:00

Hi @Bhargava-MSFT , thanks for your reply. Yesterday I've tried to answer but I haven't enabled to accomplish any tasks in the Microsoft Q&A site.

As I said, I've already used the INSERT statement specifying one time the VALUES clause, but it functions for SQL Server and not for Synapse Analyitics: a syntax error is raised.

For now, I'm constraint to write more INSERT ... VALUES ... statements.
Bhargava-MSFT 31,196 Reputation points Microsoft Employee

2023-12-04T19:12:37.8966667+00:00

Hello pmscorca,

Sorry for the delayed response.

After further research I found out that this is exptected behaviour with synapse as "The table value constructor(allows multiple rows of data to be specified in a single DML statement) is not supported in Azure Synapse Analytics."

https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16#b-inserting-multiple-rows-of-data
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

Feeding few data for some Synapse tables

Your answer