INSERT INTO... SELECT over a LinkedServer in smaller batches I've written the following code to move data from one database table, to another (over a LinkedServer);

SET NOCOUNT ON

DECLARE @ApplicationURL varchar(50),
    @LinkedServer nvarchar(50),
    @DatabaseName varchar(50)


SET @ApplicationURL = 'Test'
SET @LinkedServer = 'ABC123'
SET @DatabaseName = 'Test'



/*--------------------------------------------------------------------
    Table:  Notifications
--------------------------------------------------------------------*/
EXECUTE (
'INSERT INTO dbo.Notifications
(
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    [IsActive], 
    [UserId],
    [SourceNotificationId]
)
SELECT 
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    n.[IsActive], 
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId 
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + ''';')


This works as expected, however some tables are likely to have over a million rows, and I do not want to try and move them all at the same time, in one transaction. In my intestigation, I found this post; Breaking a big insert query into smaller ones

I would like to use this method, but I've been unable to find out how to impliment this with Dynamic SQL. Has anybody done something like this before?

Thanks!
You could split them up in batches inside the dynamic SQL statement. 

An example applied to your insert statement, per 1000 rows with the use of OFFSET ... FETCH :

EXECUTE (

'
DECLARE @RowsProcessed int = 0;
DECLARE @Batch int = 1000;
DECLARE @rowcount int = 1;

WHILE @rowcount!= 0
BEGIN

'INSERT INTO dbo.Notifications
(
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    [IsActive], 
    [UserId],
    [SourceNotificationId]
)
SELECT 
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    n.[IsActive], 
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId 
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + '''
ORDER BY n.NotificationId 
OFFSET @RowsProcessed ROWS 
FETCH NEXT @Batch ROWS ONLY;

SET @rowcount = @@ROWCOUNT;

SET @RowsProcessed += @Batch;
END
')


Note that you have to add an ORDER BY with the OFFSET ... FETCH. I have chosen the NotificationId  field of the dbo.notifications table. You could change this if needed.

Here is a smaller working db&lt;>Fiddle example.