SQL Challenge

Hi Guys



Not sure if there is a way to do this, but if there is someone on here will
know how!!



If I have 2 files, File A and File B that have the same record format as
follows:



File A

Field A (Key)

Field B (Key)

Field C

Field D

Field E



File B

Field A (Key)

Field B (Key)

Field C

Field D

Field E





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)



Example



File A


Field A

Field B

Field C

Field D

Field E


2

1

Text12

Text22

Changed


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335


5

1

New

New

New



File B


Field A

Field B

Field C

Field D

Field E


1

1

Text1

Text2

Text3


2

1

Text12

Text22

Text33


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335



File C


Field A

Field B

Field C

Field D

Field E

Record Status


1

1

Text1

Text2

Text3

D


2

1

Text12

Text22

Changed

U


3

1

Text123

Text23

Text334

X


4

1

Text1234

Text24

Text335

X


5

1

New

New

New

I



Any help with this will be useful, thanks



Neill