Well, ultimately breaking it into blocks gives me complete control over the transaction log size. In this specific case, disk space is more important than performance. I can trade performance for disk space by reducing the block size. Also, the fixed block size means that the transaction log will not increase in size as a result of more source data. This solution seems to be acceptable.

I ended up with this:

DECLARE @i INT, @max-2 INT, @blocksize INT

SET @i = 0

SET @blocksize = 50000

SELECT @max-2 = MAX(id) FROM temp.OrderPrep

WHILE @i <= @max-2

BEGIN

IF @debug = 1 SELECT @i AS , @max-2 AS [MAX]

INSERT INTO fact.CustomerOrder

SELECT

-- SELECT List removed for brevity and privacy

FROM

temp.OrderPrep OP

LEFT JOIN dim.Customer C on C.CustomerID = OP.CustomerID

OUTER APPLY Lookup.dbo.fn_SalesRegion(

CASE

WHEN C.CustomerType = 'OEM-N' then 'NAT'

WHEN C.Channel = 'EXPORT' then 'EXP'

ELSE ShipToState

END, ShipToCountry) SR

OUTER APPLY Lookup.dbo.fn_IRRegion(ShipToState, ShipToZipcode, ShipToCountry) IRR

WHERE OP.[id] <= @i AND (OP.[id] > @i - @blocksize)

IF @debug = 1 PRINT 'Checkpoint ' + CAST(@i AS VARCHAR(50))

CHECKPOINT

SET @i = @i + @blocksize

END

Which really isn't much more code than the original script. It's not the most elegant solution in the world, but it works. Frankly, I've already spent too much time on this. File bloat, however, can become serious, so it was imperative that I spend however much time was needed to solve it the problem.

Besides SSIS, I can't think of any other ways of moving the data. I've avoided SSIS for a couple of reasons. The first is because I am unsure about the OUTER APPLY capabilities via the UDFs. The other is because this code works, and I want to minimize the possibility of introducing new bugs. That second reason applies to any other method of moving data.