Working on Insert followed by update of all inserted records.

Posted on 2004-10-11
Last Modified: 2006-11-17
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
Question by:kenbrien
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 13

Expert Comment

ID: 12277244
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))

Expert Comment

ID: 12277267
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.


Author Comment

ID: 12277306
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'.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


Author Comment

ID: 12277366
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.

Author Comment

ID: 12285531
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?
LVL 13

Accepted Solution

danblake earned 500 total points
ID: 12285551
@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.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question