The problem I’m having
exploring dbt and have a question. We’re on sql-server and we have some code that bathes loading into the destination table.
staging table may have 30 million records (or more). We don’t want to merge this into final table with one insert (for a few diff reasons) so we batch the insert in increments of 1 million.
See example below - other procs are merging, insert and update.
DECLARE @EndID INT = (SELECT MAX(RowID) FROM stagetable); DECLARE @CurrentEndID INT WHILE @StartID <= @EndID BEGIN SET @CurrentEndID = @StartID + @BatchSize -1 insert into destTable (<cols>) SELECT <cols> FROM stagetable a WHERE RowID >= @StartID AND RowID <= @CurrentEndID and not exists (select * from desttable t where t.key = a.key) SET @StartID += @BatchSize end