Go Premium for a chance to win a PS4. Enter to Win


Working on Insert followed by update of all inserted records.

Posted on 2004-10-11
Medium Priority
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'.
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.


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 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

972 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