SET NOCOUNT ON
DECLARE @intRowCount int,
        @intLoop int,
        @intTotalRows int,
        @intRowsDone int,
        @dtStart datetime

SELECT @intRowCount = 1,   -- Force first loop
       @intLoop = 0,       -- Initialise
       @intRowsDone = 0,
       @dtStart = GetDate()

SELECT @intTotalRows = COUNT(*)
FROM   dbo.SourceTable AS S
WHERE  S.Value1 IS NOT NULL

WHILE @intRowCount >= 1
BEGIN
    SELECT @intLoop = @intLoop + 1
    PRINT 'Loop: ' + CONVERT(varchar(20), @intLoop)
          + ', Processed: ' + CONVERT(varchar(20), @intRowsDone) + ' of ' + CONVERT(varchar(20), @intTotalRows)
          + '. Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtStart, GetDate())) + ' seconds'

    INSERT INTO dbo.TargetTable(JoinID, Step, Value)
    SELECT TOP 100000 S.JoinID, 1, S.Value1    -- Adjust Batch size here
    FROM dbo.SourceTable AS S
    WHERE     S.Value1 IS NOT NULL
          AND S.JoinID > COALESCE((SELECT MAX(D.JoinID) FROM dbo.TargetTable AS D WHERE D.Step = 1), '')
    ORDER BY S.JoinID
    SELECT @IntRowCount = @@ROWCOUNT, @intRowsDone = @intRowsDone + @@ROWCOUNT

    -- Make optional TLog backup every N loops
END
SET NOCOUNT OFF

quote:

---

Originally posted by Kristen

I don't see why you need dynamic SQL? Although I don't suppose it makes much difference, but as complexity increases it will make it harder to debug - given there are only 20 columns I would "unravel" it into a static SQL script

Doing an insert of 100Million records, in one go, will a) take a while and b) use a huge amount of log space - although if many of the rows have NULL for a value then the number of inserts will be smaller / maybe very small.

My approach for batch-processing large numbers of records is to get all the PK IDs into a temporary table, and then process the data in reasonably sized batches. (On a live system I will also monitor the time that each batch takes and adjust the batch size accordingly - so that if the system gets busy,. batches take longer, then batch size will reduce - and conversely grow when the system is not busy).

However, given that you are only inserting the JoinID and Value (plus a "type" indicator [Step]) then that is much the same as the Temp table size anyway. I would still be inclined to do it in batches.

If you are NOT using Simple Recovery Model then make TLog backups every so often - e.g. every 10 loops

SET NOCOUNT ON
DECLARE @intRowCount int,
        @intLoop int,
        @intTotalRows int,
        @intRowsDone int,
        @dtStart datetime

SELECT @intRowCount = 1,   -- Force first loop
       @intLoop = 0,       -- Initialise
       @intRowsDone = 0,
       @dtStart = GetDate()

SELECT @intTotalRows = COUNT(*)
FROM   dbo.SourceTable AS S
WHERE  S.Value1 IS NOT NULL

WHILE @intRowCount >= 1
BEGIN
    SELECT @intLoop = @intLoop + 1
    PRINT 'Loop: ' + CONVERT(varchar(20), @intLoop)
          + ', Processed: ' + CONVERT(varchar(20), @intRowsDone) + ' of ' + CONVERT(varchar(20), @intTotalRows)
          + '. Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtStart, GetDate())) + ' seconds'

    INSERT INTO dbo.TargetTable(JoinID, Step, Value)
    SELECT TOP 100000 S.JoinID, 1, S.Value1    -- Adjust Batch size here
    FROM dbo.SourceTable AS S
    WHERE     S.Value1 IS NOT NULL
          AND S.JoinID > COALESCE((SELECT MAX(D.JoinID) FROM dbo.TargetTable AS D WHERE D.Step = 1), '')
    ORDER BY S.JoinID
    SELECT @IntRowCount = @@ROWCOUNT, @intRowsDone = @intRowsDone + @@ROWCOUNT

    -- Make optional TLog backup every N loops
END
SET NOCOUNT OFF

The downside with this is that after some rows have been added to [TargetTable] then finding more rows to add, in the next batch, which are not ALREADY created will become slower. Presumably you have a Unique Clustered Index on [TargetTable} (JoinID, Step) and an index on [SourceTable] (JoinID) - ideally the Clustered Index. On that basis I have added an ORDER BY on [SourceTable] (JoinID) so that rows will be added monotonically at the end of the clustered index, and a WHERE clause which provides the start JoinID; I think this will be faster than using NOT EXISTS.

And alternative would be to BCP out the data, pre-sorted into clustered index order of [TargetTable] and then BCP the data back into the new table. You could drop the SourceTable inbetween - in order to replaim that space if you wished.

---