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!