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
  • 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'.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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