Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    4

    Unanswered: Updating Inventory Levels T-SQL Stage Cursor/Nested Loop

    Hello All,

    By no means am i SQl Expert, i'm a former Staff Accountant who learnt the basics from our former CFO by sitting down with him as he constructed querys to create reports in Crystal & Toad. Along the way he gave me projects which required creating queries, custom tables that did inserts,updates, etc etc. I essentially picked up everything i know about t-sql from google and learning the syntax.

    Enough about me...

    We had an issue with our POS system which wiped out all inventory levels (not that it was accurate to being with anyway). What better time than now to hire a 3rd party vendor (RGIS) to come in and do inventory counts at our 50 national retail stores. Well last night they did an inventory count which provided a SKU, Qty on Hand.

    I created a bulk insert to upload this csv file. From there i inserted it into another table and ran a few update statements to retrieve RecId's (PK), product_detailno's, & locationid's.

    So now i have this table with a SKU, Location, RecId (PK), Qty and i want to run a loop which will take this info and update our cst_product_loc_details table. Out of the 4000 qty's it only seems to update 135 qty's. What am i doing wrong?


    DECLARE @TempRecID as INT
    DECLARE @TempRGIS_Qty as INT
    DECLARE @TempLocation as INT

    DECLARE Stage_Cursor CURSOR FOR SELECT RecID, RGIS_Qty, Loc FROM cst_RGISCount WHERE RECID IS NOT NULL

    OPEN Stage_Cursor
    FETCH NEXT FROM Stage_Cursor INTO @TempRecID, @TempRGIS_Qty, @TempLocation

    WHILE @@FETCH_STATUS = 0

    BEGIN
    UPDATE cst_Product_Loc_Details SET prddtl_qty_on_hand = @TempRGIS_Qty WHERE RecId = @TempRecID and Locationid = @TempLocation
    FETCH NEXT FROM Stage_Cursor INTO @TempRecID, @TempRGIS_Qty, @TempLocation

    END
    CLOSE Stage_Cursor

    DEALLOCATE Stage_Cursor

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, lose the cursors wherever possible!

    To see what you've got, I'd use:
    Code:
    SELECT
       Coalesce(prod.RecId, rgis.RecID) AS RecId
    ,  Coalesce(prod.LocationId, rgis.Loc) AS Location
    ,  rgis.RGIS_Qty
    ,  prod.prddtl_qty_on_hand
       FROM cst_RGISCount AS rgis
       FULL OUTER JOIN cst_Product_Loc_Details AS prod
          ON (prod.RecId = rgis.RecId
          AND prod.LocationID = rgis.Loc
       ORDER BY 1, 2, 3, 4
    Note that a NULL quantity indicates that the RecId/Location pair has no match for that table. Once you get the data in your RGIS staging table so it is happy, then I'd use:
    Code:
    UPDATE cst_Product_Loc_Details
       SET prddtl_qty_on_hand = rgis.RGIS_Qty
       FROM cst_RGISCount AS rgis
       WHERE  rgis.RecId = cst_Product_Loc_Details.RecId
          AND rgis.Loc = cst_Product_Loc_Details.Locationid
    ...to fix the production table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    4
    Thank you so much! That worked perfectly.

Posting Permissions

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