DECLARE @BookInventory TABLE (TitleID INT, Title NVarchar(50), Quantity Int)
DECLARE @BookOrder TABLE (TitleID INT, Title NVarchar(50), Quantity Int)
DECLARE @BookChangeTracking TABLE (TitleID INT, OldQuantity Int, NewQuantity Int,ChangeDate DateTime)
 
INSERT @BookInventory 
SELECT 1, 'The Catcher in the Rye', 6 UNION ALL
SELECT 2, 'Pride and Prejudice', 3 UNION ALL 
SELECT 3, 'The Great Gatsby', 0 UNION ALL 
SELECT 5, 'Jane Eyre', 0 UNION ALL 
SELECT 6, 'Catch 22', 0 UNION ALL
SELECT 8, 'Slaughterhouse Five', 4; 
 
INSERT @BookOrder 
SELECT 1, 'The Catcher in the Rye', 3 UNION ALL
SELECT 3, 'The Great Gatsby', 0 UNION ALL 
SELECT 4, 'Gone with the Wind', 4  UNION ALL
SELECT 5, 'Jane Eyre', 5 UNION ALL
SELECT 7, 'Age of Innocence', 8; 

MERGE @BookInventory bi 
USING @BookOrder bo ON bi.TitleID = bo.TitleID  
WHEN MATCHED AND  bi.Quantity + bo.Quantity = 0 THEN 
DELETE
WHEN MATCHED THEN 

INSERT INTO @BookChangeTracking(TitleID,OldQuantity,NewQuantity,ChangeDate)
VALUES(bi.TitleID,bi.Quantity,bi.Quantity+bo.Quantity,getdate())

UPDATE SET bi.Quantity = bi.Quantity + bo.Quantity 

WHEN NOT MATCHED BY TARGET THEN
INSERT (TitleID, Title, Quantity) 
VALUES (bo.TitleID, bo.Title,bo.Quantity);

select * from @BookInventory

WHEN MATCHED THEN BEGIN

INSERT INTO @BookChangeTracking(TitleID,OldQuantity,NewQuantity,ChangeDate)
VALUES(bi.TitleID,bi.Quantity,bi.Quantity+bo.Quantity,getdate())

UPDATE SET bi.Quantity = bi.Quantity + bo.Quantity 
END