1

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

Gopinath Rajee
  • 400
  • 5
  • 20
  • Hi @Gopinath Rajee, please correct me if I understand you wrong in the answer. – Joseph Xu Dec 22 '20 at 08:23
  • I'm just exploring various ways of migrating code from SQLServer 2012 to AzureSQL PaaS. Simple deletes will work with sp_execute_remote but updates and deletes involving complex joins with tables from other databases might be an issue if they are in a transaction/application. – Gopinath Rajee Dec 22 '20 at 17:43

1 Answers1

1

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'''
Joseph Xu
  • 5,607
  • 2
  • 5
  • 15