I have a staging table with data that gets fed in from flat files. I need to insert that data into another table, but my query is not working. It works, it just doesn't insert all the coorect records, so I need to fix the query.
The staging table gets records inserted and then updated data causes duplicate records to be inserted. I need the most current records. Here is a sample of data to help illustrate my issue. For this sample data I will need records 2 and 4 inserted, however, my query is now only selecting record 4. Please help. Thank you. Here is my query.
INSERT INTO MY_TBL2
SELECT * FROM MY_TBL1 W
WHERE LOADDATE = (SELECT MAX(LOADDATE)
FROM MY_TBL2 V
WHERE W.BCODE = V.BCODE)
order by BCODE
BCODE SAMPLEID ACTIVITY MEASUREMENT ITEMNAME LOADDATE
99999 1 act1 meas1 Item1 10/3/2012
99999 1 act1 meas1 Item1 10/4/2012
99999 2 act2 meas2 Item2 10/3/2012
99999 2 act2 meas2 Item2 10/4/2012