Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Set-based INSERTS rather than a loop

    I have a table with a series of rows for period 1 - these always exist

    drop table tblmain
    create table tblmain (period int, rfreq int, pfreq int)
    insert tblmain (period , rfreq , pfreq )
    select 1 , 1 , 1
    union all
    select 1 , 1 , 3
    union all
    select 1 , 1 , 6
    union all
    select 1 , 3 , 3
    union all
    select 1 , 3 , 6
    union all
    select 1 , 6 , 6
    union all
    select 1 , 6 , 12


    select * from tblmain
    period rfreq pfreq
    1 1 1
    1 1 3
    1 1 6
    1 3 3
    1 3 6
    1 6 6
    1 6 12
    */


    I need to do a whole lot of inserts with incremented period values up to and including
    a number assigned in a different table, FMaxes based on JOINing the two tables on the rfreq column


    CREATE TABLE FMaxes (RFreq INT , MaxF INT)
    insert FMaxes (RFreq , MaxF)
    select 1 , 5
    union all
    select 3 , 3
    union all
    select 6 , 2


    select * from FMaxes
    RFreq MaxF
    1 5
    3 3
    6 2


    Ive been scratching my head for hours trying to figure out a set-based approach rather than a loop
    solution which I could produce

    The desired result would be inserts from period 2 onwards - result set below



    select * from tblmain

    period rfreq pfreq
    1 1 1
    1 1 3
    1 1 6
    1 3 3
    1 3 6
    1 6 6
    1 6 12

    2 1 1
    2 1 3
    2 1 6
    2 3 3
    2 3 6
    2 6 6
    2 6 12

    -- no period 3 or higher for rfreq value 6 because table FMaxes holds an MaxF value of 2 etc
    3 1 1
    3 1 3
    3 1 6
    3 3 3
    3 3 6

    -- no period 4 or higher for rfreq value 3 because table FMaxes holds an MaxF value of 3 etc
    4 1 1
    4 1 3
    4 1 6

    5 1 1
    5 1 3
    5 1 6

    Thanks in advance

  2. #2
    Join Date
    Oct 2005
    Posts
    37
    I've renamed one of the columns in this table which might make it clearer MaxPeriod instead of MaxF

    CREATE TABLE FMaxes (RFreq INT , MaxPeriod INT)
    insert FMaxes (RFreq , MaxPeriod)
    select 1 , 5
    union all
    select 3 , 3
    union all
    select 6 , 2


    select * from FMaxes
    RFreq MaxPeriod
    1 5
    3 3
    6 2

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    Sorry I dont understand ur puzzle.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Oct 2005
    Posts
    37

    Unhappy

    Sorry I didn't make it clear - table tblmain has an existing series of rows all with Period value 1.
    I need to insert new rows into this table with incremented period values from 2 up to the value set in table FMaxes matching FMaxes.RFreq to tblmain.rfreq
    so using the data posted in FMaxes, I will INSERT rows from Period 2 to Period 5 for tblmain.rfreq value 1, Period 2 to Period 3 for tblmain.rfreq value 1 and just Period 2 for tblmain.rfreq

    Hope that clarifies things a bit

  5. #5
    Join Date
    Oct 2005
    Posts
    37

    Angry

    Oops

    Should read

    I need to insert new rows into this table with incremented period values from 2 up to the value set in table FMaxes matching FMaxes.RFreq to tblmain.rfreq
    so using the data posted in FMaxes, I will INSERT rows from Period 2 to Period 5 for tblmain.rfreq value 1, Period 2 to Period 3 for tblmain.rfreq value 3 and just Period 2 for tblmain.rfreq tblmain.rfreq value 6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •