Insert ... values vs Insert Select in a Synapse dedicated SQL pool

Question

Hi,

in a stored procedure on a Synapse dedicated SQL pool I need to implement the insert on a new row in a table.

In terms of performance, is it better to use the INSERT ... VALUES statement or the INSERT ... SELECT statement (specifying the necessary variables first declared)?

Thanks

Answer 1

Hi @pmscorca,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

When it comes to performance, the INSERT ... VALUES statement is generally faster than the INSERT ... SELECT statement. This is because the INSERT ... VALUES statement inserts a single row into the table, whereas the INSERT ... SELECT statement inserts multiple rows into the table.

However, the performance difference between the two statements may not be significant, and it ultimately depends on the specific use case. In some cases, the INSERT ... SELECT statement may be more efficient, especially if you need to insert multiple rows into the table at once.

I would recommend testing both statements with your specific data and workload to determine which one performs better for your use case.

Reference doc on INSERT statement in Azure synapse analytics is here.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.