Query problem - JOINS

[apwestgarth]

HI I have a view from an external system and a table which I take a copy of the data for maximum uptime. I have a job which Updates the data if anything changes, the update procedure works but now I'm having a problem writing an insert query which Inserts any new data from the view into the table on my production server. every time this runs it inserts all of the data in the view again, so each time it runs it adds duplicate rows, with each turn 74 rows. Can anyone advise me on how to extract only new data and insert it. The dfee number is unique.


My current syntax is

INSERT INTO TEMPTABLE ET
SELECT ED.SCH_DFEE, ED.SCH_NAME, ED.SCH_TO_DATE, ED.ADR_NUMBER, ED.ADR_STREET, ED.ADR_LOCALITY, ED.ADR_TOWN, ED.ADR_COUNTY, ED.ADR_POSTCODE, ED.SCH_TEL, ED.SCH_FAX, ED.SCH_EMAIL_SEC, ED.PTT_DESC, ED.CONTACT_SNAME, ED.CONTACT_INIT, ED.[Chair of Governors], ED.SCH_WEB, ED.SCHFIL_DESC, ED.SCHFIL_FILE, ED.[School Type], ED.ADR_UPRN, ED.ADR_GRIDREF_E, ED.ADR_GRIDREF_N, ED.tblLU_SCH_TYPE, ED.STAT_DESC, ED.STAT_ID, ED.PHASE_DESC, ED.PHASE_ID
	FROM SQL1.ED.vw_View ED
	LEFT JOIN EducationTemp ET ON ED.SCH_DFEE = ET.SCH_DFEE
	AND ED.SCH_NAME = ET.SCH_NAME
	AND ED.SCH_TO_DATE = ET.SCH_TO_DATE
	AND ED.ADR_NUMBER = ET.ADR_NUMBER
	AND ED.ADR_STREET = ET.ADR_STREET
	AND ED.ADR_LOCALITY = ET.ADR_LOCALITY
	AND ED.ADR_TOWN = ET.ADR_TOWN
	AND ED.ADR_COUNTY = ET.ADR_COUNTY
	AND ED.ADR_POSTCODE = ET.ADR_POSTCODE
	AND ED.SCH_TEL = ET.SCH_TEL
	AND ED.SCH_FAX = ET.SCH_FAX
	AND ED.SCH_EMAIL_SEC = ET.SCH_EMAIL_SEC
	AND ED.PTT_DESC = ET.PTT_DESC
	AND ED.CONTACT_SNAME = ET.CONTACT_SNAME
	AND ED.CONTACT_INIT = ET.CONTACT_INIT
	AND ED.[Chair of Governors] = ET.[Chair of Governors]
	AND ED.SCH_WEB = ET.SCH_WEB
	AND ED.SCHFIL_DESC = ET.SCHFIL_DESC
	AND ED.SCHFIL_FILE = ET.SCHFIL_FILE
	AND ED.[School Type] = ET.[School Type]
	AND ED.ADR_UPRN = ET.ADR_UPRN
	AND ED.ADR_GRIDREF_E = ET.ADR_GRIDREF_E
	AND ED.ADR_GRIDREF_N = ET.ADR_GRIDREF_N
	AND ED.tblLU_SCH_TYPE = ET.tblLU_SCH_TYPE
	AND ED.STAT_DESC = ET.STAT_DESC
	AND ED.STAT_ID = ET.STAT_ID
	AND ED.PHASE_DESC = ET.PHASE_DESC
	AND ED.PHASE_ID = ET.PHASE_ID

Thanks

Andrew Westgarth
Web Developer

 

[johnherman]

I would create a sequence data field using either my own or a standard database sequence. Make sure the sequence is incremented with each insert in the transaction system. Then, have a small table to keep track of the last sequence number loaded and add a WHERE clause to your INSERT statement to check for rows greater than the LAST_LOADED_SEQ.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[johnherman]

Forgot to finish. Then select MAX of that sequence number and UPDATE the little table containing the last sequence loaded with that MAX value.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright