Re: SQL Challenge

["sjl" <sjl_abc@xxxxxxxxxxx>]

Neill -

I'm an idiot...
I forgot the query to show the inserts, so I'll restate all FOUR of them in 
one place:


Query 1 (Changed):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'U' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
  (
   B.FieldC <> A.FieldC or
   B.FieldD <> A.FieldD or
   B.FieldE <> A.FieldE
   )
)


Query 2 (unchanged):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'X' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
  (
   B.FieldC = A.FieldC AND
   B.FieldD = A.FieldD AND
   B.FieldE = A.FieldE
   )
)


Query 3 (Deletes):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'D' from FileB B
Where NOT Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
)


Query 4 (Inserts):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'I' from FileA A
Where NOT Exists
(
select *from fileB B
where (A.FieldA = B.FieldA and A.FieldB=B.FieldB)
)


Neill wrote:
File A is a new version of File B, i.e. at one point it contained exactly
the same records as File B. However over time File A has had records
inserted, deleted and updated.



Can anybody think of a way using sql that gives me a third file FILE C, that
has the exact same format as FILE A and FILE B, except for one extra field
called Record Status. So that file C contains all of the original records
from FILE B that are no longer in FILE A (where record status = D, Deleted),
all of the new records in FILE A (where record status = I, Inserted) , all
of the Records that are in FILE A that have been changed from FILE B (where
record status = U, Updated) and finally all records in FILEA that have not
changed from FILE B( where status = X, unchanged)