I have set up a linked server connection from my on-premise to Azure called AZURE. When I run the following code, it errors out:
DECLARE @DynamicSQL NVARCHAR(MAX)='
MERGE [AZURE].[Test].[dbo].[Persons] AS TARGET
USING [Test].[dbo].Persons AS SOURCE ON (TARGET.ID = SOURCE.ID)
-- When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.LastName <> SOURCE.LastName OR TARGET.FirstName <> SOURCE.FirstName OR TARGET.Age <> SOURCE.Age
THEN
UPDATE
SET TARGET.LastName = SOURCE.LastName,
TARGET.FirstName = SOURCE.FirstName,
TARGET.Age = SOURCE.Age;'
EXECUTE (@DynamicSQL) AT AZURE
The error message is the following:
The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.
Attempted to make the MERGE statement as simple as possible for testing purposes but the same error is still returned.