Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spikev2
Starting Member
6 Posts |
 Posted - 2009-01-21 : 09:41:57
|
Hi all.
Hope the following info is clear. I basically have a job that runs 3 stored procedures.
But I am getting some strange anomalies ie Records not appearing in t2 or t3 but since disappeared. Not in t1 but were at some stage.
My gut feeling is that the t1 external update is not happening correctly if there is nothing wrong with the other procedures.
My other thought was using Exist instead of outer joins to check if records exist.
I have 4 tables
T1 Movements – Updated every 30 mins
PK on the following 2 fields
Order varchar (6)
Panel int
T2 Assignments
PK on the following fields
Order varchar (6)
Panel int
T3 Archive
Pk on following fields
Order varchar (6)
Panel int
T4 additional table info
Job is run every 30 mins which clears the data in T1 then adds it from a unix box.
My 3 step job runs as follows after the above.
1.Archive – looks at T2 . If no corresponding order in T1 then write record to T3
Code as follows:
Insert into T3 Archive
SELECT assmorder,asspanelcode,frame,clearknit,T2.priority
FROM T2 LEFT OUTER JOIN
T1 ON T2.order = T1.ORDER AND
T2.panelcode = T1.PANELCODE
WHERE (T1.ORDER IS NULL)
2.Delete – looks at t2. If no corresponding order in T1 then delete record from t2
DELETE FROM t2
FROM t2 LEFT OUTER JOIN
T1 ON t2.order = t1.order AND
T2.panelcode = t1.PANELCODE
WHERE (t1.ORDER IS NULL)
3.Add – looks at t1 if record does not exist in t2 then add it.
insert into t2
SELECT t1.MORDER, t1.PANELCODE, NULL AS frame,
dbo.SQ_MAKING_ORDERS.CLEARKNIT_DATE AS clearknit, t4.PRIORITY,GETDATE() as AssignedPriority
FROM t4 INNER JOIN
t1 ON t4.ORDER = t1.MORDER LEFT OUTER JOIN
t2 ON t1.MORDER = t2.assmorder AND
t1.PANELCODE = t2.asspanelcode
WHERE (t2.assmorder IS NULL)
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-21 : 09:46:06
|
| show some data sample to illustrate discrepancy |
 |
|
|
|
|
|
Job not working as expected