One of the simplest ways to move data between tables in chunks is:
DECLARE @CHUNK_SIZE int = 10000;
DECLARE @RC int = @CHUNK_SIZE;
WHILE @RC >0
BEGIN
INSERT INTO TargetTable (Mycolumns)
SELECT TOP @CHUNK_SIZE Mycolumns
FROM SourceTable
WHERE Conditions
;
SET @RC = @@ROWCOUNT
;
END
Where the conditions need to exclude already processed rows.
The performance is mostly depending on these conditions (and indexes).
You can for example use
NOT EXISTS
, or use an ordered query and set a variable = Max(ID) from the output clause, or you can remove processed rows from the Sourcetable.
But as you've already been told, we don't have enough information to tell you the best way.