Table [XI]  29,026 Rows (size 20,128Kb).
Table [XII] 531,958 Rows (size 50,168Kb).
Time taken for entire script: 1.51s.

Table [XI]  117,877 Rows (size 17,000Kb).
Table [XII] 4,000,443 Rows (size 512,824Kb).
Time taken for entire script: 2.04s.

Table [XI]  29,543 Rows (size 20,880Kb).
Table [XII] 538,302 Rows (size 68,000Kb).
Time taken for entire script: 44.38s.

DECLARE @DbName NVARCHAR(128);
SET @DbName = (SELECT TOP 1 [DbName]
               FROM [IPACostAdmin]..[TmpSpecialOptions]);
DECLARE @FilterSql NVARCHAR(MAX);
SET @FilterSql = (SELECT TOP 1 [AdditionalSQL]
                  FROM [IPACostAdmin]..[TmpSpecialOptions]);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @SQL1 NVARCHAR(MAX);
DECLARE @SQL2 NVARCHAR(MAX);
SET @SQL1 =
    'INSERT INTO [' + @DbName + ']..[Episode]
        ([Fields1], ..., [FieldN])';
SET @SQL2 =
'SELECT
     [Fields1], ..., [FieldN]
FROM [B1A] ' + @FilterSql + ';';
SET @SQL = @SQL1 + @SQL2;
EXEC(@SQL);
GO