• GrassHopper - Friday, October 20, 2017 2:55 PM

    On another update, I am getting the same error msg and I can't find the needle in the haystack.  I was thinking of doing an update on a portion of the data at a time.  In other words, break up the update into 10 passes until it fails and then i can concentrate on a smaller set of data to look at for the problem.  The total size is 130,571 records.  It has an ID column that is unique, but not in serial order.  How can I break this update up into 10 update passes?

    i.e.  - ID numbers:
    117915
    117916
    117917
    118300
    118301
    118302
    118512
    118513

    SQL Code:

    Update XLSHdr_Promo_TescoUK
    Set PTL_EndDate_Dt =     Case WHEN #Staging.PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(date, #Staging.PTL_EndDate, 104)  -- 27.01.2014
                                WHEN #Staging.PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' THEN CONVERT(date, #Staging.PTL_EndDate, 4)  -- 27.07.16
                            END
    From #Staging
    Where XLSHdr_Promo_TescoUK.ID = #Staging.ID     

    A loop like this will do the trick.  You can keep changing the WHERE clause to do different batches
    CREATE TABLE #Staging (
      ID      INT NOT NULL
    , PTL_EndDate   VARCHAR(20)
    , Clean_PTL_EndDate DATE
    );

    INSERT INTO #Staging (ID, PTL_EndDate)
    VALUES ( 117915, '27.01.2014' )
      , ( 117916, '15.27.2014' )
      , ( 117916, '01.27.14' )
      , ( 117917, '2014.01.27' )
      , ( 118301, '27.01.2014' )
      , ( 118302, '01.27.2014' );

    DECLARE @BatchSize INT = 1; -- Start with a big number, and keep making it smaller

    BEGIN TRY
    WHILE EXISTS (SELECT 1 FROM #Staging
          WHERE Clean_PTL_EndDate IS NULL
          AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
           OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
         )
    BEGIN
      WITH cteBatch AS (
      SELECT TOP (@BatchSize)
        ID
       , PTL_EndDate
       , Clean_PTL_EndDate
       , YearPart = PARSENAME(PTL_EndDate, 1)
       , MonthPart = CASE WHEN PARSENAME(PTL_EndDate, 2) > 12 THEN PARSENAME(PTL_EndDate, 3) ELSE PARSENAME(PTL_EndDate, 2) END
       , DayPart = CASE WHEN PARSENAME(PTL_EndDate, 2) > 12 THEN PARSENAME(PTL_EndDate, 2) ELSE PARSENAME(PTL_EndDate, 3) END
      FROM #Staging
      WHERE Clean_PTL_EndDate IS NULL
       AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
        OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
      ORDER BY ID
      )
      UPDATE cteBatch
      SET Clean_PTL_EndDate = CONVERT(date, DayPart + '.' + MonthPart + '.' + YearPart, 104);

      WAITFOR DELAY '00:00:00.100'; -- Pause for 100ms
    END;
    END TRY
    BEGIN CATCH
    -- Somewhere in this batch you should find your bad apple.
    -- If this returns no data, then the specific pattern has been taken care of
    SELECT TOP (@BatchSize) *
    FROM #Staging
    WHERE Clean_PTL_EndDate IS NULL
    AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
      OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
    ORDER BY ID;
    END CATCH