USE TEMPDB
GO

CREATE TABLE Products  (
	[ProductID] [int] NOT NULL IDENTITY CONSTRAINT PK_Products PRIMARY KEY CLUSTERED,
	[Name] [nvarchar](100) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[Colour] [nvarchar](15) NULL,
	[FrameSize] AS CAST(RIGHT([ProductNumber],2) AS smallint) ) ;
GO

INSERT INTO Products  (	[Name], 	[ProductNumber],	[Colour] )
VALUES 
( 'Mountain-500 Black, 24', 'BK-M18B-24', 'Black'),
( 'Mountain-500 Black, 28', 'BK-M18B-28', 'Black'),
( 'Mountain-500 Black, 32', 'BK-M18B-32', 'Black') ;
GO


-- Make sure Grid Mode is on!
SELECT * FROM Products
GO

INSERT INTO Products  (	[Name], 	[ProductNumber],	[Colour] )
OUTPUT inserted.*
VALUES 
( 'Mountain-500 Silver, 24', 'BK-M18S-24', 'Silver'),
( 'Mountain-500 Silver, 28', 'BK-M18S-28', 'Silver'),
( 'Mountain-500 Silver, 32', 'BK-M18S-32', 'Silver') ;
GO

-- Create a load table
CREATE TABLE Products_load  (
	[Name] [nvarchar](100) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[Colour] [nvarchar](15) NULL )
GO

INSERT INTO Products_load  ( [Name], [ProductNumber], [Colour] )
SELECT [Name], [ProductNo], [Colour]
FROM ( VALUES 
( 'Mountain-500S Black, 28', 'BK-M18B-28', 'Black'),
( 'Mountain-500S Black, 32', 'BK-M18B-32', 'Black'),
( 'Mountain-500S Black, 36', 'BK-M18B-36', 'Black'),
( 'Mountain-500S Black, 40', 'BK-M18B-40', 'Black'),
( 'Road-750 Black, 24', 'BK-R19B-24', 'Black'),
( 'Road-750 Black, 28', 'BK-R19B-28', 'Black'),
( 'Road-750 Black, 32', 'BK-R19B-32', 'Black')
) ld ( [Name], 	[ProductNo],	[Colour]  ) ;
GO

-- New/Updated Data
SELECT [Name], [ProductNumber], [Colour] FROM Products_load ;
GO

SELECT * FROM Products ;

-- Doing Updates/Inserts and Deletes on matches to [ProductNumber]
-- Change Name to "Mountain-500S" for "BK-M18B-28", "BK-M18B-32", "BK-M18B-36"
-- Add "BK-M18B-36", "BK-M18B-40", "BK-R19B-24", "BK-R19B-28", "BK-R19B-32"
-- Delete "BK-M18B-24", "BK-M18S-24", "BK-M18S-28", "BK-M18S-32"
MERGE Products AS p
USING 
	(SELECT [Name], [ProductNumber], [Colour] FROM Products_load ) as src
		ON (p.[ProductNumber] = src.[ProductNumber] )
WHEN MATCHED AND ( p.[Name] <> src.[Name] OR p.[Colour] <> src.[Colour] )
    THEN UPDATE SET p.[Name] = src.[Name], p.[Colour] = src.[Colour]
WHEN NOT MATCHED
	THEN  INSERT  ( [Name], [ProductNumber], [Colour] )
			VALUES ( src.[Name], src.[ProductNumber], src.[Colour] )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE ;

-- Updated/Inserted/Deleted data
SELECT * FROM Products
ORDER BY ProductID ;
GO

-- Change Some Data
UPDATE Products
SET Colour = 'Noir'
WHERE Colour = 'Black' ;
GO

DELETE FROM Products_load 
WHERE [ProductNumber] = 'BK-M18B-40' ;
GO

INSERT INTO Products_load ([Name], [ProductNumber], [Colour] )
VALUES 
( 'City Bike-3000 Red, 24', 'CB-T30U-24', 'Red'),
( 'City Bike-4000 Red, 28', 'BK-T40U-28', 'Red'),
( 'City Bike-4000 Red, 32', 'BK-T40U-32', 'Red');
GO

SELECT * FROM Products_load
ORDER BY [ProductNumber]
GO

-- Doing Updates/Inserts and Deletes on matches to productId with OUTPUT clauses
MERGE Products AS p
USING 
	(SELECT [Name], [ProductNumber], [Colour] FROM Products_load ) as src
		ON (p.[ProductNumber] = src.[ProductNumber])
WHEN MATCHED AND ( p.[Name] <> src.[Name] OR p.[Colour] <> src.[Colour] )
    THEN UPDATE SET p.[Name] = src.[Name], p.[Colour] = src.[Colour]
WHEN NOT MATCHED
	THEN  INSERT  ( [Name], [ProductNumber], [Colour] )
			VALUES ( src.[Name], src.[ProductNumber], src.[Colour] )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE 
    OUTPUT $ACTION, COALESCE(inserted.ProductId, deleted.ProductId ) AS ProductId,
    inserted.[Name] AS [New Name], inserted.[ProductNumber]  AS [New ProductNumber], inserted.[Colour]  AS [New Colour], inserted.[FrameSize]  AS [New FrameSize],
    deleted.[Name] AS [Old Name], deleted.[ProductNumber] AS [Old ProductNumber], deleted.[Colour] AS [Old Colour], deleted.[FrameSize]  AS [Old FrameSize]
     ;
GO		

-- Clean Up
DROP TABLE Products 
GO

DROP TABLE Products_load
GO