question

sakuraime avatar image
1 Vote"
sakuraime asked sakuraime commented

MERGE INTO Azure synapse

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

I have two table

[dbo].[table_product] and [dbo].[ex_table_product], and would like to merge [dbo].[ex_table_product] data into [dbo].[table_product] . Using the following code

DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO [dbo].[table_product] AS Target
USING (select ProductKey,ProductLabel,ProductName from [dbo].[ex_table_product])
AS Source (ProductKey,ProductLabel,ProductName)
ON (Target.ProductKey = Source.ProductKey)
WHEN MATCHED THEN
UPDATE SET ProductLabel = Source.ProductLabel
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductKey,ProductLabel,ProductName) VALUES (ProductKey,ProductLabel,ProductName)
OUTPUT $action INTO @SummaryOfChanges;



just think that if MERGE INTO already supported in Azure Synapse , as well as Azure synapse studio . cause there are so many syntax warning right there .

43164-capture.jpg


azure-synapse-analytics
capture.jpg (55.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sakuraime,

We have not received a response from you. Please suggest if below suggested approaches are helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member

0 Votes 0 ·

Hi @sakuraime,

We still have not heard back from you. Following up to check if below suggested approaches are helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

0 Votes 0 ·
VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered HarithaMaddi-MSFT commented

Instead of creating table variable @SummaryOfChanges, try creating temp table #SummaryOfChanges and insert data to it

Looks like table variables are not supported on Azure synapse

https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/13875030-support-table-variables


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks. apart from the table variable .

are there any syntax error from the merge statement?

May I know the source table can be an external table (polybase )
??

0 Votes 0 ·

Hi @sakuraime,

As @VaibhavChaudhari mentioned, table variables are not supported in Synapse yet. I understand error message is misleading and product team is working on enhancing those and you will soon see accurate error displayed. Yes, the source table can be an external table as well.

Please let us know for further queries and we will be glad to assist. Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.



0 Votes 0 ·
DanielGoucher avatar image
0 Votes"
DanielGoucher answered sakuraime commented

Hi sakuraime,

According to the documentation you cannot get output from a merge statement in Synapse. So I suspect removing the output portion of your query will solve the syntax error.

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=azuresqldb-current

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

any real example for Azure synapse ??? which works???

0 Votes 0 ·
DriesCanfyn-1076 avatar image
0 Votes"
DriesCanfyn-1076 answered DriesCanfyn-1076 rolled back

Same issue here. Should I enable the preview features somewhere or is the merge statement not supported in some regions atm? (mine is located in Western Europe)

53227-screenshot-1.png



screenshot-1.png (35.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SunilP-5929 avatar image
0 Votes"
SunilP-5929 answered sakuraime commented

I am also facing same issue, I don't know why giant Microsoft could not solve this issue, very sad :(

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

125912-image.png




this is a preview , we not expecting more

0 Votes 0 ·
image.png (30.2 KiB)