How to establish only rows that have been updated or deleted

Question

I would like help with code that will show which rows have been added/inserted or deleted.

For example in the table called 'allprior' the following rows were deleted.

3 Ferrari 355 London Executive Prestige Vehicles United Kingdom 135600 5500 2200 1950 169500

and

27 Triumph TR5 Peter McLuckie United Kingdom 10120 320 750 150 12650

In the table maxversion the following row was added/inserted

29 masearati 7 Carlton Patterson United Kingdom 4400 500 750 150 5500

My code will let you know the rows that were deleted.

SELECT
  *
FROM dbo.allprior
LEFT OUTER JOIN dbo.maxversion
  ON allprior.ID = maxversion.ID
WHERE maxversion.ID IS NULL

However, I need help showing not just the rows that were deleted but the row that was added i.e.

29	masearati	7	Carlton Patterson	United Kingdom	4400	500	750	150	5500

Also, if it could be possible to add a column showing rows that were either added or deleted with a U and D that would also be very helpful.

Below is sample table

CREATE TABLE maxversion (
    ID int,
    MakeName varchar(50),
    ModelName varchar(50),
    CustomerName varchar(50),
    CountryName varchar(50),
    Cost int,
    RepairsCost int,
    PartsCost int,
    TransportInCost int,
    SalePrice int)

INSERT maxversion VALUES
(1,'Ferrari','Testarossa','Magic Motors','United Kingdom',52000,2175,1500,750,65000),
(2,'Ferrari','355','Snazzy Roadsters','United Kingdom',176000,5500,2200,1950,220000),
(3,'Ferrari','355','London Executive Prestige Vehicles','United Kingdom',135600,5500,2200,1950,169500),
(5,'Porsche','944','Casseroles Chromes','France',15960,1360,500,150,19950),
(6,'Porsche','911','M. Pierre Dubois','France',15680,890,500,150,19600),
(7,'Porsche','944','M. Pierre Dubois','France',7160,500,750,150,8950),
(8,'Porsche','924','WunderKar','Germany',9200,500,750,150,11500),
(9,'Porsche','911','Birmingham Executive Prestige Vehicles','United Kingdom',15600,660,500,150,19500),
(10,'Porsche','924','Posh Vehicles Ltd','United Kingdom',7160,360,750,150,8950),
(11,'Porsche','944','Wonderland Wheels','United Kingdom',6800,250,750,150,8500),
(12,'Porsche','911','Convertible Dreams','United Kingdom',18360,500,750,150,22950),
(13,'Aston Martin','DB6','SuperSport S.A.R.L.','France',44800,1785,500,550,56000),
(14,'Aston Martin','DB4','Magic Motors','United Kingdom',23600,500,750,150,29500),
(15,'Aston Martin','DB5','Birmingham Executive Prestige Vehicles','United Kingdom',39600,2500,1500,550,49500),
(16,'Aston Martin','DB6','Eat My Exhaust Ltd','United Kingdom',60800,3250,750,750,76000),
(17,'Aston Martin','DB6','Alexei Tolstoi','United Kingdom',55600,1490,1500,750,69500),
(18,'Aston Martin','DB5','Sondra Horowitz','United States',29200,1950,500,550,36500),
(19,'Aston Martin','Virage','Theo Kowalski','United States',98872,2175,2200,750,123590),
(20,'Bentley','Flying Spur','Wonderland Wheels','United Kingdom',64400,500,750,750,80500),
(21,'Mercedes','280SL','La Bagnole de Luxe','France',18360,550,500,150,22950),
(22,'Alfa Romeo','Giulia','Convertible Dreams','United Kingdom',6956,400,750,150,8695),
(23,'Jaguar','XJS','SuperSport S.A.R.L.','France',15600,290,750,150,19500),
(24,'Jaguar','XK150','Alexei Tolstoi','United Kingdom',18392,390,750,150,22990),
(25,'Jaguar','XJS','Alexei Tolstoi','United Kingdom',18080,660,750,150,22600),
(26,'Jaguar','XK120','Peter McLuckie','United Kingdom',12480,1100,500,150,15600),
(28,'Triumph','TR4','Theo Kowalski','United States',4400,500,750,150,5500),
(29,'masearati','7','Carlton Patterson','United Kingdom',4400,500,750,150,5500)

SELECT * FROM maxversion

Thank you

Answer 1

Check this query, or show missing details:

select allprior.*, 'D' as Indicator
from allprior
left outer join maxversion on allprior.ID = maxversion.ID
where maxversion.ID is null
union all
select maxversion.*, 'A' as Indicator
from allprior
right outer join maxversion on allprior.ID = maxversion.ID
where allprior.ID is null
union all
select *, 'U'
from
(
	select *
	from maxversion
	except
	select *
	from allprior
) u
where ID in (select ID from allprior)
order by ID