Post reply

Create a stored procedure that will add data to table based on a value from a different table

  • May 22, 2008 at 10:08 pm

    #96307

    I have a table named Courses which contains course information as well as a column named No_Units, which gives the number of units this course should have. I have another table named Units that contains units for all of the courses. The Unit table does not always contain the amount of units for the courses that the Course table No_units shows. I would like to create a stored procedure that I can call that will automatically create the missing units in the Unit table. I basically just need to add blank enties in the Units table. Any Ideas on how this stored procedure should look?

    Thanks in advance

  • June 24, 2008 at 6:46 pm

    #833657

    This old trick using a Tally table may help... I did not modify it to match your tables... see the link in my signature if you want that 😉

    --===== This makes a little test table according to the post

    CREATE TABLE dbo.TableA (Col1 VARCHAR(15), Col2 INT)

    INSERT INTO dbo.TableA

    (Col1, Col2)

    SELECT 'widget_a',3 UNION ALL

    SELECT 'widget_b',8 UNION ALL

    SELECT 'widget_c',7

    --===== This makes rows based on the data in Col2

    -- Just add this to an insert.

    SELECT a.*

    FROM dbo.TableA a

    CROSS JOIN dbo.Tally t

    WHERE t.N <= a.Col2

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 26, 2008 at 10:55 am

    #834852

    Also you might want to consider a design change.

    Units should be the source to determine your number of units not a column in Courses. This is why you have this issue.

    But the example before should do the job just keep in mind key pointsof data that may have to be in Units besides the unit number.

Viewing 3 posts - 1 through 2 (of 2 total)

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