the while dont loop correctly to the end

  • i try to understand why  my procedure do not  respect the loop in batches of 10 until the end. i must lauche 3 time to finish.

    CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
    AS
    /********************************************************************************************************************************************/
    /* The purpose of this procedure is to insert logs. There are 11 common columns */
    /* Parameters: */
    /*Input: @DateParam */
    /* Output: */
    /* Version: 1.0 Date: 15/09/2023 Author: */
    /********************************************************************************************************************************************/
    /*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
    /**/
    /********************************************************************************************************************************************/
    BEGIN

    DECLARE @Status INT
    DECLARE @ErrorMessage NVARCHAR(4000)
    -- Determines the start and end date based on the @DateParam parameter
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @TryenreID INT

    DECLARE @BatchSize INT = 10; -- Batch size
    DECLARE @RowCount INT; -- Total number of records
    DECLARE @StartRow INT = 1; -- Starting row of the batch
    DECLARE @EndRow INT; -- Ending row of the batch

    BEGIN TRY
    IF @DateParam IS NULL
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    END
    ELSE
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
    END

    -- Get the total number of records in the Geusa table
    SELECT @RowCount = COUNT(*)
    FROM dbo.Geusa u
    WHERE u.code = 0
    AND u.status IN (0, 6, 23, 30)
    AND u.UseTime >= @StartDate AND u.UseTime < @EndDate
    AND u.visualid ='40131001110124597401239'
    AND NOT EXISTS (SELECT 1
    FROM Tryenre r
    WHERE r.VisualID = u.VisualID
    and r.EntryTime = u.UseTime
    and r.Status = u.Status
    and r.acp = u.acp
    )

    -- While there are records to process
    WHILE @StartRow <= @RowCount
    BEGIN

    BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;

    -- Calculate the end row of the batch
    SET @EndRow = @StartRow + @BatchSize - 1;

    --INSERT operation into the Tryenre table
    INSERT INTO dbo.Tryenre
    (TryenreID
    ,EntryTime
    ,VisualID
    ,AccessCode
    ,ACP
    ,Status
    ,Qty
    ,SerialNo
    ,Override
    ,BankNo
    ,RecordVersion
    ,LastUpdate
    ,LastUpdatedBy
    ,OperationID
    ,ScannedVisualID
    ,Attraction
    ,Facility
    )
    SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID
    ,UseTime
    ,VisualID
    ,AccessCode
    ,ACP
    ,Status
    ,Qty
    ,SerialNo
    ,Override
    ,BankNo
    ,RecordVersion
    ,LastUpdate
    ,LastUpdatedBy
    ,OperationID
    ,ScannedVisualID
    ,AttractionID
    ,FacilityID
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    ,u.UseTime
    ,COALESCE(s.VisualID ,u.VisualID)VisualID
    ,u.AccessCode
    ,u.ACP
    ,u.Status
    ,u.Qty
    ,u.SerialNo
    ,u.Override
    ,u.BankNo
    ,u.RecordVersion
    ,u.LastUpdate
    ,u.LastUpdatedBy
    ,u.OperationID
    ,u.ScannedVisualID
    ,u.AttractionID
    ,u.FacilityID
    FROM Geusa u
    LEFT OUTER JOIN dbo.PersuKetsi s ON CONVERT(VARCHAR(40),s.BaseID) = u.VisualID
    WHERE u.code = 0
    AND u.status IN (0, 6, 23, 30)
    AND u.UseTime >= @StartDate AND u.UseTime < @EndDate
    AND u.visualid ='40131001110124597401239'
    AND NOT EXISTS (SELECT 1
    FROM dbo.Tryenre r
    WHERE r.VisualID = u.VisualID
    AND r.EntryTime = u.UseTime
    AND r.Status = u.Status
    AND r.acp = u.acp
    )
    ) AS SubQuery
    WHERE RowNum BETWEEN @StartRow AND @EndRow

    -- Update the starting row for the next batch
    SET @StartRow = @EndRow + 1;

    -- Commit the transaction
    COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;

    END

    END TRY
    BEGIN CATCH
    -- In case of error, rollback the transaction if it was started
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;

    SET @ErrorMessage = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Propagate the error for further handling by the application
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

    -- Set output parameters for Update success
    SET @Status = 0

    END;
    GO
  • that's because you  don't start from the same set of data due to the "not exists" credential. ( which checks for existance in the target table !! )

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I would say that your left outer join to PersuKetsi results in you having more rows being processed than on the query you get the count(*) from.

    change your first query to include it

     

  • i take all your remarks and it's working and it's works . thanks

    CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
    AS
    /********************************************************************************************************************************************/
    /* The purpose of this procedure is to insert logs. There are 11 common columns */
    /* Parameters: */
    /*Input: @DateParam */
    /* Output: */
    /* Version: 1.0 Date: 15/09/2023 Author: */
    /********************************************************************************************************************************************/
    /*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
    /**/
    /********************************************************************************************************************************************/
    BEGIN

    DECLARE @Status INT
    DECLARE @ErrorMessage NVARCHAR(4000)
    -- Determines the start and end date based on the @DateParam parameter
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @TryenreID INT

    DECLARE @BatchSize INT = 10; -- Batch size
    DECLARE @RowCount INT; -- Total number of records
    DECLARE @StartRow INT = 1; -- Starting row of the batch
    DECLARE @EndRow INT; -- Ending row of the batch

    BEGIN TRY
    IF @DateParam IS NULL
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    END
    ELSE
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
    END

    -- Get the total number of records in the Geusa table
    SELECT @RowCount = COUNT(*)
    FROM dbo.Geusa u
    WHERE u.code = 0
    AND u.status IN ( 0, 6, 23, 30 )
    AND u.UseTime >= @StartDate
    AND u.UseTime < @EndDate
    AND u.visualid = '40131001110124597401239'

    -- While there are records to process
    WHILE @StartRow <= @RowCount
    BEGIN

    BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;

    -- Calculate the end row of the batch
    SET @EndRow = @StartRow + @BatchSize - 1;

    --INSERT operation into the Tryenre table
    INSERT INTO dbo.Tryenre
    (
    TryenreID,
    EntryTime,
    VisualID,
    AccessCode,
    ACP,
    Status,
    Qty,
    SerialNo,
    Override,
    BankNo,
    RecordVersion,
    LastUpdate,
    LastUpdatedBy,
    OperationID,
    ScannedVisualID,
    Attraction,
    Facility
    )
    SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID,
    ,SubQuery.UseTime
    ,coalesce(convert(varchar(40),s.BaseID) ,SubQuery.VisualID)VisualID
    ,SubQuery.AccessCode
    ,SubQuery.ACP
    ,SubQuery.Status
    ,SubQuery.Qty
    ,SubQuery.SerialNo
    ,SubQuery.Override
    ,SubQuery.BankNo
    ,SubQuery.RecordVersion
    ,SubQuery.LastUpdate
    ,SubQuery.LastUpdatedBy
    ,SubQuery.OperationID
    ,ScannedVisualID
    ,SubQuery.AttractionID
    ,SubQuery.FacilityID
    FROM
    (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
    u.UseTime,
    u.VisualID,
    u.AccessCode,
    u.ACP,
    u.Status,
    u.Qty,
    u.SerialNo,
    u.Override,
    u.BankNo,
    u.RecordVersion,
    u.LastUpdate,
    u.LastUpdatedBy,
    u.OperationID,
    u.ScannedVisualID,
    u.AttractionID,
    u.FacilityID
    FROM Geusa u

    WHERE u.code = 0
    AND u.status IN ( 0, 6, 23, 30 )
    AND u.UseTime >= @StartDate
    AND u.UseTime < @EndDate
    AND u.visualid = '40131001110124597401239'
    ) AS SubQuery
    LEFT JOIN dbo.SuperTickets s On convert(varchar(40),s.BaseID) = SubQuery.VisualID
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    AND NOT EXISTS (SELECT 1
    FROM dbo.Tryenre r
    WHERE r.VisualID = SubQuery.VisualID
    AND r.EntryTime = SubQuery.UseTime
    AND r.Status = SubQuery.Status
    and r.acp = SubQuery.acp
    )
    -- Update the starting row for the next batch
    SET @StartRow = @EndRow + 1;

    -- Commit the transaction
    COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;

    END

    END TRY
    BEGIN CATCH
    -- In case of error, rollback the transaction if it was started
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;

    SET @ErrorMessage = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Propagate the error for further handling by the application
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

    -- Set output parameters for Update success
    SET @Status = 0

    END;
    GO
  • I prefer to use OFFSET/FETCH NEXT to run batches of records in a loop.  I've updated your query to reflect that.

    • I replaced your @RowCount, @StartRow, and @EndRow with a single variable: @CurCount.
    • I removed your query to calculate the total number of rows.
    • I removed your subquery to calculate a ROW_NUMBER(), because we don't need a row number using this method.
    • I added a sort (feel free to replace that with a sort based on some [preferably unique] index).
    • Why don't you just use Geusa.VisualID instead of the potentially NULL SuperTickets.BaseID.

      • coalesce(convert(varchar(40),s.BaseID) ,SubQuery.VisualID)VisualID always gives the same results as the much simpler Geusa.VisualID.  Use the simpler formula.

    • Is there a mismatch in the data types for SuperTickets.BaseID and Geusa.VisualID?

      • Performance suffers greatly when you use a formula in a join.
      • I would expect the data types for these fields to match, so the formula shouldn't be required.

    Here is the updated query.  Note, that this is completely UNTESTED.

    CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
    AS
    /********************************************************************************************************************************************/
    /* The purpose of this procedure is to insert logs. There are 11 common columns */
    /* Parameters: */
    /*Input: @DateParam */
    /* Output: */
    /* Version: 1.0 Date: 15/09/2023 Author: */
    /********************************************************************************************************************************************/
    /*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
    /**/
    /********************************************************************************************************************************************/
    BEGIN

    DECLARE @Status INT
    DECLARE @ErrorMessage NVARCHAR(4000)
    -- Determines the start and end date based on the @DateParam parameter
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @TryenreID INT

    DECLARE @BatchSize INT = 10; -- Batch size
    /* Replace three separate variables with just one. */
    DECLARE @CurCount INT = @BatchSize;

    BEGIN TRY
    IF @DateParam IS NULL
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    END
    ELSE
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
    END

    /* Removed unnecessary query to calculate total rows. */

    -- While there are records to process
    WHILE @CurCount = @BatchSize
    BEGIN

    BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;

    /* Removed extraneous parameter setting here. */

    --INSERT operation into the Tryenre table
    INSERT INTO dbo.Tryenre
    (
    TryenreID,
    EntryTime,
    VisualID,
    AccessCode,
    ACP,
    Status,
    Qty,
    SerialNo,
    Override,
    BankNo,
    RecordVersion,
    LastUpdate,
    LastUpdatedBy,
    OperationID,
    ScannedVisualID,
    Attraction,
    Facility
    )
    SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID,
    ,u.UseTime
    ,u.VisualID -- Why use the complex formula here?
    ,u.AccessCode
    ,u.ACP
    ,u.Status
    ,u.Qty
    ,u.SerialNo
    ,u.Override
    ,u.BankNo
    ,u.RecordVersion
    ,u.LastUpdate
    ,u.LastUpdatedBy
    ,u.OperationID
    ,u.ScannedVisualID
    ,u.AttractionID
    ,u.FacilityID
    FROM Geusa u
    LEFT JOIN dbo.SuperTickets s On convert(varchar(40),s.BaseID) = SubQuery.VisualID -- Do you really need to do the conversion here?
    WHERE u.code = 0
    AND u.status IN ( 0, 6, 23, 30 )
    AND u.UseTime >= @StartDate
    AND u.UseTime < @EndDate
    AND u.visualid = '40131001110124597401239'
    AND NOT EXISTS (SELECT 1
    FROM dbo.Tryenre r
    WHERE r.VisualID = SubQuery.VisualID
    AND r.EntryTime = SubQuery.UseTime
    AND r.Status = SubQuery.Status
    and r.acp = SubQuery.acp
    )
    ORDER BY u.VisualID
    OFFSET 0 ROWS
    FETCH NEXT @BatchSize ROWS ONLY;

    -- Changed the WHILE loop conditions update
    SET @CurCount = @@ROWCOUNT;

    -- Commit the transaction
    COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;

    END

    END TRY
    BEGIN CATCH
    -- In case of error, rollback the transaction if it was started
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;

    SET @ErrorMessage = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Propagate the error for further handling by the application
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

    -- Set output parameters for Update success
    SET @Status = 0

    END;
    GO

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Why don't you take the loop out and do it all in one statement?

  • i applicate your remarks but i raise the following error

     

    The NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified
    USE [galaxytymtest]
    GO

    CREATE OR ALTER PROCEDURE dbo.XXX_RestoreyrteeneRFromegasuXX (@DateParam DATE = NULL)
    AS
    /********************************************************************************************************************************************/
    /* Parametre : */
    /*Entree : @DateParam*/
    /* sortie :*/
    /* Version : 1.0 Date : 15/09/2023 Auteur:*/
    /********************************************************************************************************************************************/
    /*
    EXECUTION
    exec dbo.XXX_RestoreyrteeneRFromegasuXX @DateParam='20210813'--49939 joinutre 47102
    select count(*) from yrteeneR
    */
    /********************************************************************************************************************************************/
    BEGIN

    DECLARE @ApplicationName VARCHAR(4) = 'RestoreyrteeneR'
    DECLARE @ProcessName VARCHAR(30) = OBJECT_NAME(@@PROCID)
    DECLARE @EventDataIn VARCHAR(200) = ''
    DECLARE @EventDataOut VARCHAR(200) = ''
    DECLARE @EventStatusDescr VARCHAR(100) = ''
    DECLARE @Status INT
    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME

    DECLARE @BatchSize INT = 10000; -- Taille du lot
    DECLARE @CurCount INT = @BatchSize;

    --truncate table dbo.yrteeneR

    BEGIN TRY

    IF @DateParam IS NULL
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
    END
    ELSE
    BEGIN
    SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
    SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
    END
    -- Changed the WHILE loop conditions update
    WHILE @CurCount = @BatchSize
    BEGIN
    BEGIN TRANSACTION XXX_RestoreyrteeneRFromegasu;

    INSERT INTO dbo.yrteeneR
    (yrteeneRID
    ,EntryTime
    ,VisualID
    ,AccessCode
    ,ACP
    ,Status
    ,Qty
    ,SerialNo
    ,Override
    ,BankNo
    ,RecordVersion
    ,LastUpdate
    ,LastUpdatedBy
    ,OperationID
    ,ScannedVisualID
    ,Attraction
    ,Facility
    )
    SELECT NEXT VALUE FOR yrteeneRIDSequence AS yrteeneRID
    ,u.UseTime
    ,u.VisualID
    ,u.AccessCode
    ,u.ACP
    ,u.Status
    ,u.Qty
    ,u.SerialNo
    ,u.Override
    ,u.BankNo
    ,u.RecordVersion
    ,u.LastUpdate
    ,u.LastUpdatedBy
    ,u.OperationID
    ,u.ScannedVisualID
    ,u.AttractionID
    ,u.FacilityID
    FROM egasu u
    WHERE u.code = 0
    AND u.status IN (0, 6, 23, 30)
    AND u.UseTime >= @StartDate
    AND u.UseTime < @EndDate
    AND NOT EXISTS (SELECT 1
    FROM dbo.yrteeneR r
    WHERE r.VisualID = u.VisualID
    AND r.EntryTime = u.UseTime
    AND r.Status = u.Status
    and r.acp = u.acp
    )
    ORDER BY u.VisualID
    OFFSET 0 ROWS
    FETCH NEXT @BatchSize ROWS ONLY;

    -- Changed the WHILE loop conditions update
    SET @CurCount = @@ROWCOUNT;

    -- Commit the transaction
    COMMIT TRANSACTION XXX_RestoreyrteeneRFromegasu;

    END;

    END TRY
    BEGIN CATCH
    -- In case of error, rollback the transaction if it was started
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION XXX_RestoreyrteeneRFromegasu;

    SET @ErrorMessage = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Propagate the error for further handling by the application
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;

    -- Set output parameters for Update success
    SET @Status = 0

    END;
    GO

     

    • This reply was modified 5 months ago by  phine.
  • Here are some recommendations:

    1. Remove the explicit transaction - it isn't needed because you have a single insert being performed and that will - by default - be an implicit transaction.
    2. Use THROW in the CATCH block instead of RAISERROR

    A couple of things I would also change:

    1. Set the WHILE loop to be always true - add a check to determine how many rows have been inserted.  If the number of rows inserted is less than your batch size, break from the loop.  This can save an extra iteration in the loop
    2. Use INSERT TOP (@BatchSize) instead of trying to only select the batch number of rows.  The NOT EXISTS determines if there are any more rows to be inserted.
    3. Get rid of the check for @DateParm being NULL - this can be done in the assignment.  Instead of convert to date and then a convert to datetime - use the 'standard' formula to return a datetime with the time set to midnight.
    SET @StartDate = DATEADD(HH,  3, DATEADD(day, DATEDIFF(day, 0, COALESCE(@DateParm, GETDATE())), 0));
    SET @EndDate = DATEADD(HH, 27, DATEADD(day, DATEDIFF(day, 0, COALESCE(@DateParm, GETDATE())), 0));

    Example loop:

    WHILE 1 = 1
    BEGIN

    INSERT TOP (@BatchSize) INTO dbo.yrteeneR
    (yrteeneRID
    ,EntryTime
    ,VisualID
    ,AccessCode
    ,ACP
    ,Status
    ,Qty
    ,SerialNo
    ,Override
    ,BankNo
    ,RecordVersion
    ,LastUpdate
    ,LastUpdatedBy
    ,OperationID
    ,ScannedVisualID
    ,Attraction
    ,Facility
    )
    SELECT NEXT VALUE FOR yrteeneRIDSequence AS yrteeneRID
    ,u.UseTime
    ,u.VisualID
    ,u.AccessCode
    ,u.ACP
    ,u.Status
    ,u.Qty
    ,u.SerialNo
    ,u.Override
    ,u.BankNo
    ,u.RecordVersion
    ,u.LastUpdate
    ,u.LastUpdatedBy
    ,u.OperationID
    ,u.ScannedVisualID
    ,u.AttractionID
    ,u.FacilityID
    FROM egasu u
    WHERE u.code = 0
    AND u.status IN (0, 6, 23, 30)
    AND u.UseTime >= @StartDate
    AND u.UseTime < @EndDate
    AND NOT EXISTS (SELECT 1
    FROM dbo.yrteeneR r
    WHERE r.VisualID = u.VisualID
    AND r.EntryTime = u.UseTime
    AND r.Status = u.Status
    and r.acp = u.acp
    );

    SET @CurCount = @@ROWCOUNT;
    IF @CurCount < @BatchSize BREAK --No more rows to be inserted
    END

    As a further optimization, I would set the default constraint in the table to use NEXT VALUE FOR and eliminate it from the procedure.  If you did that, you could then use an ORDER BY in the query to specify the order to be inserted and it will be generated in that order.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply