Post reply

While Loop

  • August 24, 2006 at 7:54 am

    #115110

    I have the following query. It was initially a cursor and I'm trying to refrain from the cursor as it takes to long to complete.

    I seem to have this query in an infinite loop, can some one explain if what I'm trying to achieve is possible without a cursor?

    declare @cnt int

    declare @cntInc int

    DECLARE @maxId bigint

     
    set @cntInc = 1

    set @cnt = (SELECT count(*) from Policy_Product)

    SELECT @maxId = MAX(ExternalId) FROM focus_transaction_classic..Policy_IdGenerator

     
    WHILE @cntInc <= @cnt

     

    BEGIN

     INSERT INTO Policy_Product_New (Policy_ProductId, PolicyId, ProductClass, ContinuousInsuranceYears,

        ContinuousInsuranceLoadingPercentage, PreviousInsurerName,

        ExternalId, RevisionId, PreviousRevisionId)

     SELECT Policy_ProductId, PolicyId, ProductClass, ContinuousInsuranceYears,

        ContinuousInsuranceLoadingPercentage, PreviousInsurerName,

        @maxId, RevisionId, PreviousRevisionId

     FROM Policy_Product

     

     SET @maxid = @maxid + 1

     SET @cntInc = @cntInc + 1

    END

    Kindest Regards,

  • August 24, 2006 at 8:26 am

    #656595

    Maybe you are trying to do something like:

    DECLARE @t TABLE

    -- or create a temp table

    (

     Policy_ProductId int not null primary key

     ,RowNum int identity not null )

    INSERT @t (Policy_ProductId)

    SELECT Policy_ProductId

    FROM Policy_Product

    INSERT INTO Policy_Product_New

    (

     Policy_ProductId

     ,PolicyId

     ,ProductClass

     ,ContinuousInsuranceYears

     ,ContinuousInsuranceLoadingPercentage

     ,PreviousInsurerName

     ,ExternalId

     ,RevisionId

     ,PreviousRevisionId)

    SELECT P.Policy_ProductId

     ,PolicyId

     ,ProductClass

     ,ContinuousInsuranceYears

     ,ContinuousInsuranceLoadingPercentage

     ,PreviousInsurerName

     ,D.MaxID + T.RowNum

     ,RevisionId

     ,PreviousRevisionId

    FROM Policy_Product P

     JOIN @t T on P.Policy_ProductId = T.Policy_ProductId

     CROSS JOIN

     ( SELECT MAX(ExternalId) as MaxID

      FROM focus_transaction_classic..Policy_IdGenerator) D

     

  • August 24, 2006 at 8:32 am

    #656598

    It looks complex but i'll try it.

    Remeber that the key thing here is that I need to increment @maxID by 1 for each row.

    Kindest Regards,

  • August 24, 2006 at 8:54 am

    #656602

    There is no WHERE clause on your INSERT inside the loop.

    Each time it executes it is replicating the whole Policy_Product table into Policy_Product_New.

    I would create a 2 column temp table, with an IDENTITY column, and seed it with the current max value. The 2nd column is Policy_ProductID. You insert all your Policy_ProductID's into this table, and the IDENTITY column maps each ID to it's new sequence number, ready for the INSERT. No need for a while loop.

    [Edit] Like this ...

     

    -- Temp table to generate new sequence numbers for

    -- each Policy_ProductID

    Create Table #IDGenerator (

      Policy_ProductID int,

      SequenceNumber int identity

    )

    -- Get the current MAX value

    Declare @MaxID as int

    SELECT @maxId = MAX(ExternalId) FROM focus_transaction_classic..Policy_IdGenerator

    -- Reseed the temp table's identity value to the current max

    DBCC CheckIdent ('#IDGenerator', RESEED, @MaxID)

    -- Generate the new sequence numbers

    INSERT INTO #IDGenerator (Policy_ProductID)

    SELECT Policy_ProductId

    FROM Policy_Product

    -- Create the new records in the Policy_Product_New table

    INSERT INTO Policy_Product_New

       (Policy_ProductId, PolicyId, ProductClass, ContinuousInsuranceYears,

        ContinuousInsuranceLoadingPercentage, PreviousInsurerName,

        ExternalId, RevisionId, PreviousRevisionId)

     SELECT pp.Policy_ProductId, PolicyId, ProductClass, ContinuousInsuranceYears,

        ContinuousInsuranceLoadingPercentage, PreviousInsurerName,

        t.SequenceNumber, RevisionId, PreviousRevisionId

     FROM Policy_Product As pp

     INNER JOIN #IDGenerator As t

        ON (t.Policy_ProductID = pp.Policy_ProductID)

     

  • August 24, 2006 at 9:00 am

    #656606

    PW. I'm going round in circles. Can you please give me an example as I'm confused as to how to incorporate your suggestion with my

    SELECT @maxId = MAX(ExternalId) FROM focus_transaction_classic..Policy_IdGenerator

    @maxID is the value for ExternalID in the INSERT.

    Kindest Regards,

  • August 24, 2006 at 9:01 am

    #656608

    See edit above to add code.

  • August 24, 2006 at 5:01 pm

    #656747

    Thanks PW. I'll give it a go.

    Kindest Regards,

  • August 24, 2006 at 9:03 pm

    #656759

    WHAT???

    You insert MaxID into ExternalId, and ExternalId suppose to reference some ID from some external table.

    If you just increment MaxId by 1 everytime you're doing insert what those new ExternalID's suppose to reference?

    _____________
    Code for TallyGenerator

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

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