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.
AZUREserver to understand[Test].[dbo].Personsas your local[Test].[dbo].Persons? It's going to understand[Test].[dbo].Personsas its[Test].[dbo].Persons, and it's going to reject the[AZURE].[Test].[dbo].[Persons]for being a four-part name, thus referring to a remote server. And if it didn't reject it by recognizing[AZURE]is actually its own name, it would be merging that table into itself.INSERT INTO [AZURE].[Test].[dbo].[Persons](...) SELECT ... FROM [Test].[dbo].Persons AS P INNER JOIN CHANGETABLE(CHANGES [Test].[dbo].Persons, @last_synchronization_version) AS CT ON P.ID= CT.tID AND CT.SYS_CHANGE_OPERATION = 'I';UPDATE ... FROM Remote INNER JOIN (SELECT ID,... From LocalTable INNER JOIN CHANGETABLE(CHANGES LocalTable, @last_synchronization_version) AS CT ON P.ID= CT.tID AND CT.SYS_CHANGE_OPERATION = 'U') c on Remote.ID=C.ID ;