DTS UPDATE ERROR

[bberrios]

I am trying to set up a DTS package to run every day. What needs to happen is it need to check on table for any updated records with an AGENCY_ID of 6 from the REPORT TALBE against TEMP table. If the LASTUPDATE fields do not match, then a new copy of the record needs to be appended to the TEMP table, not overwriting the old one...to keep a running log. THis is what I have, and it's not working:


INSERT INTO dbo.JCAHO_CROSSWALK
(REPORT_ID, USER_ID, VISN_ID, AGENCY_ID, SURVEY_ID, VA_FACILITY_ID, ASSIGN_FACILITY_NUM, VISIT_DATE, REPORT_DATE,
ACCREDITATION_TIME_PERIOD_ID, ACCREDITATION_ID, GRID_SCORE, SURVEYORS, REPORT_TYPE, LASTUPDATE, DETAILS_EXIST)
SELECT REPORT_ID, USER_ID, VISN_ID, AGENCY_ID, SURVEY_ID, VA_FACILITY_ID, ASSIGN_FACILITY_NUM, VISIT_DATE, REPORT_DATE,
ACCREDITATION_TIME_PERIOD_ID, ACCREDITATION_ID, GRID_SCORE, SURVEYORS, REPORT_TYPE, LASTUPDATE, DETAILS_EXIST
FROM REPORT
WHERE (AGENCY_ID = 6 AND REPORT.LASTUPDATE <> LASTUPDATE) bb

 

[MeanGreen]

Have you considered setting up an update trigger on your report table to write to the &quot;temp&quot; table when updates occur and the agency_ID = 6? This method will keep the log accurate and not rely on a batch process to maintain a log table. In the update trigger you put:

INSERT INTO dbo.JCAHO_CROSSWALK
(REPORT_ID, USER_ID, VISN_ID, AGENCY_ID, SURVEY_ID, VA_FACILITY_ID, ASSIGN_FACILITY_NUM, VISIT_DATE, REPORT_DATE, ACCREDITATION_TIME_PERIOD_ID, ACCREDITATION_ID, GRID_SCORE, SURVEYORS, REPORT_TYPE, LASTUPDATE, DETAILS_EXIST)
SELECT REPORT_ID, USER_ID, VISN_ID, AGENCY_ID, SURVEY_ID, VA_FACILITY_ID, ASSIGN_FACILITY_NUM, VISIT_DATE, REPORT_DATE, ACCREDITATION_TIME_PERIOD_ID, ACCREDITATION_ID, GRID_SCORE, SURVEYORS, REPORT_TYPE, LASTUPDATE, DETAILS_EXIST
FROM INSERTED
WHERE AGENCY_ID = 6

That should get you what you want.

 

[bberrios]

INSERTED gives me an invalid name error....any other suggestions (this is in SQL 2000) bb

 

[bberrios]

NEVERMIND....BRAIN LAPSE FOR A MINUTE bb

 

[MeanGreen]

INSERTED is only visible within the TRIGGER. Within triggers there are two additional tables called INSERTED and DELETED. You cannot see these tables within the QUERY ANALYZER. Believe me, they do exist. And the nice thing is that the inserted table contains the input values and the deleted table contains the removed values only. So that is why you can use them to populate your log table. Good Luck.

 

[bberrios]

But the issue is I also need to compare the field LASTUPDATE from both tables. I only want to insert in the JCAHO_CROSSWALK table any records that have agency_id 6 and have been updated since the last upload. bb

 

[MeanGreen]

This is the beauty of having it in a trigger. You would no longer need to run a batch file to update. For every update condition, it would write to your &quot;Temp&quot; file if the agency_id was 6. If you wanted, you could have an outer join between INSERTED and Report on agency_id and LASTUPDATE column to apply updates only where report.lastupdate is null. Format would be:

INSERT INTO dbo.JCAHO_CROSSWALK
(REPORT_ID, USER_ID, VISN_ID, AGENCY_ID, SURVEY_ID, VA_FACILITY_ID, ASSIGN_FACILITY_NUM, VISIT_DATE, REPORT_DATE, ACCREDITATION_TIME_PERIOD_ID, ACCREDITATION_ID, GRID_SCORE, SURVEYORS, REPORT_TYPE, LASTUPDATE, DETAILS_EXIST)
SELECT ins.REPORT_ID, ins.USER_ID, ins.VISN_ID, ins.AGENCY_ID, ins.SURVEY_ID, ins.VA_FACILITY_ID, ins.ASSIGN_FACILITY_NUM, ins.VISIT_DATE, ins.REPORT_DATE, ins.ACCREDITATION_TIME_PERIOD_ID, ins.ACCREDITATION_ID, ins.GRID_SCORE, ins.SURVEYORS, ins.REPORT_TYPE, ins.LASTUPDATE, ins.DETAILS_EXIST
FROM INSERTED ins left outer join REPORT rpt
on ins.agency_id = rpt.agency_id
and ins.lastupdate = rpt.lastupdate
WHERE ins.AGENCY_ID = 6 and rpt.lastupdate is null

Again... I don't think you need this added overhead if you use the INSERTED table and base the insert on the agency_id. The reason is if there is a row in INSERTED then there has been an update.

Hope this makes sense.