INSERT INTO… 在 LinkedServer 上小批量選擇

我編寫了以下程式碼將數據從一個數據庫表移動到另一個表（通過 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 + ''';')

這可以按預期工作，但是有些表可能有超過一百萬行，我不想嘗試在一個事務中同時移動它們。在我的調查中，我發現了這個文章；將大的插入查詢分解為較小的查詢

我想使用這種方法，但我一直無法找到如何使用動態 SQL 來實現它。以前有人做過這樣的事情嗎？

謝謝！

您可以在動態 SQL 語句中將它們分批拆分。

一個範例應用於您的插入語句，每 1000 行使用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
')

請注意，您必須ORDER BY在OFFSET ... FETCH. 我選擇了表格的NotificationId 欄位。dbo.notifications如果需要，您可以更改此設置。

這是一個較小的工作 db<>Fiddle 範例。

我認為這會更好。當您在遠端表上加入和過濾時，通常會發生的情況是，本地 SQL 很快就會開始拉動所有表，然後進行連接/過濾。正如您提到的，這變得昂貴且緩慢。

要保持完全動態的解決方案，您可以查看以下內容。這利用了 EXEC () AT ServerName 語法。

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)

引用自：https://dba.stackexchange.com/questions/256655