Working on Insert followed by update of all inserted records.
Posted on 2004-10-11
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
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
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
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