0

I've written the following code to move data from one database table, to another table (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 investigation, 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 implement this with Dynamic SQL. Has anybody done something like this before?

Thanks!

2

2 Answers 2

0

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<>Fiddle example.

0

I think that this will work better. What usually happens when you join and filter on remote tables is that very quickly the local SQL starts pulling all the tables over and then doing the join/filter. This gets expensive and slow as you mention.

To keep a fully dynamic solution, you might look into the following. This makes use of the EXEC () AT ServerName syntax.

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


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

DECLARE @SQLCMD NVARCHAR(4000) = N'
SELECT 
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    n.[IsActive], 
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]
FROM [' + @DatabaseName + '].[dbo].[Notifications] n
    JOIN [' + @DatabaseName + '].[dbo].[Users] u ON n.UserId = u.UserId 
    JOIN [' + @DatabaseName + '].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ' + QUOTENAME('?', N'''')

SET @SQLCMD = N'EXEC(' + '''' + @SQLCMD + '''' + ', ' + '''' + @ApplicationURL + '''' + ') AT ' + @LinkedServer + ';'

INSERT INTO dbo.Notifications 
(
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    [IsActive], 
    [UserId],
    [SourceNotificationId]
)
EXEC (@SQLCMD)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.