I have a two tables that I want to merge
Table A
| COL A | COL B | COL C |
|---|---|---|
| 1 | 2 | value |
| 2 | 2 | value |
Table B
| COL A | COL B | COL C |
|---|---|---|
| 1 | 2 | value |
| 2 | 2 | newvalue |
The merge SQL
MERGE INTO dbo.TableA AS target
USING dbo.TableB AS source
ON
(
target.ColA = source.ColA AND
target.ColB = source.ColB
)
WHEN MATCHED THEN
UPDATE SET
target.COLC = source.COLC
WHEN NOT MATCHED THEN
INSERT (COLA, COLB, COLC)
VALUES (source.COLA, source.COLB, source.COLC);
Whenever I run above code my database (Azure synapse, dedicated pool) throws following error, and then stops responding for about 20 seconds.
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
I have a suspicion that it has something to do with the Update part of the merge. because the first time I run the statement (where there isn't any data inside table A) It works just fine and inserts the two rows. But if I run it again it fails with the transport-level error
