• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Working on Insert followed by update of all inserted records.

Below is a cut of a stored procedure I am working on. I am trying to insert records from a table that is by all the fields of the table I am trying to update but also includes fields called currency and rateset (curr_id & ers_id). I think the insert is right as it inserts what I am expecting and the update seems to work as it is updating what I see in the front end I use but there is a difference in the amount of inserted versus updated records when I would expect all that were inserted to be updated and most but not all are. What differences in the conditions between the two could account for this? Am I missing something in either query that may be inserting too many of not updating enough?

/* Declare variables */
@ver_id_dest INT,          -- Version Destinatin ID
@ver_id_source INT,        -- Version Source ID
@year_id_source INT,       -- Year Source ID
@year_id_dest INT,        -- Year Destination ID            
@ers_id_source INT,      -- Exchange Rate Set Source ID
@ers_id_dest INT,            -- Exchange Rate Set Destination ID
@curr_id INT            -- Default Current ID

-- Lookup ID's
  SELECT @ver_id_dest = mem_id from ver where mem_name = 'DEC_FCST'
  SELECT @ver_id_source = mem_id from ver where mem_name = 'Actual'
  SELECT @year_id_source = mem_id from meteor.year where mem_name = '2004'
  SELECT @year_id_dest = mem_id from meteor.year where mem_name = '2005'
  SELECT @ers_id_source = mem_id from meteor.ers where mem_name = 'Actual Rate'
  SELECT @ers_id_dest = mem_id from meteor.ers where mem_name = 'Forecast Rate'
  SELECT @curr_id = mem_id from curr where mem_name = 'GBP'

--Insert into Finloc Forecast from Finper Actuals where Summary Forecast records don't exist.
  INSERT INTO finloc_base (year_id, line_id, unit_id, ver_id, cust1_id, cust2_id, cust3_id, cust4_id, P0)
  SELECT A.year_id, A.line_id, A.unit_id, A.ver_id, A.cust1_id, A.cust2_id, A.cust3_id, A.cust4_id, A.P0
       A.line_id = B.line_id AND A.unit_id = B.unit_id AND A.cust1_id = B.cust1_id AND A.cust2_id = B.cust2_id AND A.cust3_id = B.cust3_id AND A.cust4_id = B.cust4_id
      AND A.ver_id  = B.ver_id  AND A.year_id = B.year_id
    WHERE  A.UNIT_ID in (Select MEM_ID from Summary_Forecast_Units where MEM_TYPE = 'C')
      AND A.ERS_ID = @ers_id_dest AND A.CURR_ID = @curr_Id
      AND A.year_ID = @year_id_dest AND A.ver_ID = @ver_id_dest
      AND B.ver_id IS NULL

--Update Finloc Forecast from Finper Actuals for units entering Forecast at a summary level
  UPDATE finloc_base
    SET P0 = A.P0
    FROM finper_base A JOIN Summary_Forecast_Units C ON
   finloc_base.line_id = A.line_id AND finloc_base.unit_id = A.unit_id AND finloc_base.cust1_id = A.cust1_id
   AND finloc_base.cust2_id = A.cust2_id AND finloc_base.cust3_id = A.cust3_id AND finloc_base.cust4_id = A.cust4_id
      AND finloc_base.ver_id = A.ver_id AND finloc_base.year_id = A.year_id
   AND A.ver_id = @ver_id_dest AND A.year_id = @year_id_dest AND finloc_base.year_id = @year_id_dest
  AND A.ERS_ID = @ers_id_dest
  • 3
  • 2
1 Solution
This will be due to the select join criteria, and if any new records have appeared in the base tables that were not present at time of insert.
It is possible that new records have been added during this time, hence more records get updated than inserted, to solve this wrap the entirity of the operation within a transaction to ensure that insert & updated amounts are the same.  You should and can check the @@rowcount, @@error for any errors and discrepancies between the insert & update like so...

Ensure that the @@Rowcount & @@Error follow the statement just executed, otherwise you will not get the results from the last run command.
With the transaction in place you will be locking the table in its entirity for new records to be inserted whilst this procedure runs, this is the only way of ensuring consistency of your data...

/* Declare variables */
               @ROWSUPD INT,
              @ERRORS INT


   PRINT 'Error Rows Inserted does not match Rows UPDATED'
   PRINT 'RowsInserted'+cast(@ROWSINS as char(8))
   PRINT 'RowsUpdated'+cast(@ROWSUPD as char(8))
Hi kenbrien,

quick guess - some of the fields in finloc_base that you are testing are null

remember, null = null returns null, which is taken as false.

if you want to return true if two fields are both null, then

((field1 = field2) OR (field1 IS NULL AND field2 IS NULL))

or, neater, but possible diferent performance issues (and make sure works with you data - the following will return true if one is 0, the other is null):

ISNULL(field1, 0) = ISNULL(field2, 0)

for numeric fields.

kenbrienAuthor Commented:
P.S. The table called Summary_Forecast_Units is a table with a list of units and currencies and I only want to insert and update based on the unit and currency being in this and having a mem_type flag of 'C'.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

kenbrienAuthor Commented:
I am testing for null because I need to run this multiple times and saw the use of the null on the destination somewhere as a way to tell no record exists and can insert. Not positive I used it correctly. The differences in the counts have been the same even when I ran the two queries several minutes apart.

The rows inserted won't always match updated won't always match those inserted because once the records are in they won't be deleted and reinserted and should just be updated.
kenbrienAuthor Commented:
I need some help figuring this one out. Since I am running this for the first time I am thinking the insert and update should affect the same number of records and any times after that I will assume what it is updating is correct. How would I tell what records it was not updating and then why the insert didn't get all the same records it was supposed to?
@ken, the easiest way would be to make a second run-time table to just verify your results.

Any records that should be inserted are copied into table1
Any records that should be affected by the update are first copied into table 2

This way you can have a look at what records were actually changed/why.

Another way of doing the same thing is to look at Lumigent Log Explorer (Demo is available for download -- I believe) to see what you app/sproc is doing.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now