Solved

Working on Insert followed by update of all inserted records.

Posted on 2004-10-11
9
232 Views
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 */
DECLARE
@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'

BEGIN
--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
    FROM FINPER_BASE A LEFT OUTER JOIN FINLOC_BASE B ON
       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
    A.Unit_ID = C.MEM_ID AND A.CURR_ID = C.CURR_ID
WHERE
   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
0
Comment
Question by:kenbrien
  • 3
  • 2
9 Comments
 
LVL 13

Expert Comment

by:danblake
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...

BEGIN TRANSaction
/* Declare variables */
...
DECLARE @ROWSINS INT,
               @ROWSUPD INT,
              @ERRORS INT

...
...
INSERT ....
SELECT @ROWSINS = @@ROWCOUNT, @ERRORS = @@ERROR

...
UPDATE ...
SELECT @ROWSUPD = @@ROWCOUNT, @ERRORS = @@ERROR
IF @ROWSUPD <> @ROWSINS THEN
BEGIN
   PRINT 'Error Rows Inserted does not match Rows UPDATED'
   PRINT 'RowsInserted'+cast(@ROWSINS as char(8))
   PRINT 'RowsUpdated'+cast(@ROWSUPD as char(8))
END
IF @@ERROR = 0 THEN
COMMIT TRANSaction
ELSE
ROLLBACK TRANSACTION
0
 
LVL 6

Expert Comment

by:boblah
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.

Cheers!
0
 

Author Comment

by:kenbrien
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'.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:kenbrien
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.
0
 

Author Comment

by:kenbrien
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?
0
 
LVL 13

Accepted Solution

by:
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now