MoveDataTemplate.sql

/* Can be used to move a large amount of data from one table to another in chunks to keep transaction log and temp db under control */
CREATE PROCEDURE [SchemaName].[moveData]
AS
BEGIN
	DECLARE 
		 @row INT = 0
		,@step INT = 100000
		,@max INT

	SELECT @max = max(dv_keyid)
	FROM [hub].[BusinessKey]

	WHILE @row < @max
	BEGIN
		BEGIN TRANSACTION

		INSERT INTO [SchemaName].[TableName_new] 
		(
			/* columns */
		)
		SELECT 
		(
			/* columns */
		)
		FROM [SchemaName].[TableName_old]
		WHERE [dv_KeyID] BETWEEN @row + 1
				AND @row + @step;

		COMMIT TRANSACTION
		CHECKPOINT

		SELECT @row + 1
			,@row + @step
			,@max

		SET @row = @row + @step
	END
END;