I have a table in Azure Synapse Dedicated SQL POOL.
CREATE TABLE table_A ( ID int IDENTITY(1,1), ClientID varchar(10), fitrstname varchar(20), lastname varchar(30), phone varchar(20), address varchar(100), milageRun decimal(18,8) CONSTRAINT (PK_table_A) PRIMARY NONCLUSTERED ( ClientID ASC ) ) WITH ( DISTRIBUTION = ROUND_ROBIN, HEAP ) GO
Now when I am trying to do a MERGE-INSERT-UPDATE the above table using the below syntax
MERGE table_A AS TARGET USING table_B AS SOURCE ON table_A.ClientID = table_B.ClientID WHEN MATCHED THEN UPDATE SET TARGET.milageRun = SOURCE.odometerCount IF NOT MATCHED BY TARGET THEN INSERT INTO (milageRun) VALUES (SOURCE.odometerCount)
The above SQL script is throwing two different errors:
1. Msg 50000, Level 16, State 1, Line 1 Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash distributed table. 2. Can not update ID column
But the same above query works fine when I use normal On-Prem SQL Server or Azure SQL Database.
In the above example
table_A is a small table and contains < 1000 data. As per Microsoft recommendation we should use Hash distribution only when there are huge records.
So what could be the best approach?
Secondly, even if I am not updating the
ID column, why the 2nd error is coming?
Any expert advice would be helpful.