azure-sql-databaseazure-sql

DML operations on Tables in Different databases of AzureSQL PaaS

All,

We are migrating our database from SQLServer 2012 to AzureSQL PaaS DB. In lot of stored procedures, we have code as below:

USE ClaimDB1 GO

Delete ClaimDB2.dbo.Claims Where Claimdate < ‘12/01/2020’

The database ClaimDB1 and ClaimDB2 are in the same server.

Given that DML operations are not allowed on EXTERNAL TABLEs, what are the various patterns of accomplishing the same in the AzureSQL PaaS world?

Thanks, grajee

Solution

  • After you created EXTERNAL TABLEs, please use the following sql. sp_execute_remote will allow you to execute DML operation if you have the permission.

    exec sp_execute_remote 
N'<Your-remote-datasource-name>',  
N'Delete dbo.Claims  Where Claimdate < ''12/01/2020'''