gistfile1.sql

DECLARE @SomeTable TABLE (Id INTEGER PRIMARY KEY NOT NULL IDENTITY,
  ProductCompanyId INT NOT NULL, -- Company where the product is from.
	ProductId VARCHAR(100) NOT NULL, -- Unique ProductId .. but unique -per- company.
	Name VARCHAR(50),
	Price MONEY
	);


-- Seed some data.
INSERT INTO @SomeTable VALUES(1, '111111', 'Sugar', 1.58);
INSERT INTO @SomeTable VALUES(1, '222222', 'Milk', 2.17);
INSERT INTO @SomeTable VALUES(1, '13sdfsdfds', '38" LED Tv - Sony', 388.99);
INSERT INTO @SomeTable VALUES(2, 'xxxxxxxxxx', 'BroomStick', 31.75);


-- Now lets prepare our MERGE data.
DECLARE @MergeData TABLE (ProductCompanyId INT NOT NULL,
	ProductId VARCHAR(100) NOT NULL,
	Name VARCHAR(50),
	Price MONEY);


-- Output Results.
DECLARE @MergeProductsResults TABLE (
		ProductCompanyId INTEGER,
		ProductId VARCHAR(100),
		Id INTEGER
	);

INSERT INTO @MergeData VALUES (1, 'Eggs1234', 'Eggs - Hippy and Happy', 5.44);
INSERT INTO @MergeData VALUES (1, '222222', 'Milk', 0.99); -- Price reduction (incorrect price).
INSERT INTO @MergeData VALUES (1, 'asd234dsf', 'Cat food', 15.99); -- Price reduction.
INSERT INTO @MergeData VALUES (1, 'toothaaaahhhh', 'Toothpaste', 3.90); -- Price reduction.
INSERT INTO @MergeData VALUES (1, '222222', 'Milk', 1.99); -- Price reduction (Fixed pricing typo).




MERGE INTO @SomeTable T
	USING @MergeData S
	ON T.ProductCompanyId = S.ProductCompanyId AND T.ProductId = S.ProductId
	WHEN MATCHED THEN
		UPDATE
		SET T.Name = S.Name,
			T.Price = S.Price
	WHEN NOT MATCHED THEN
		INSERT (ProductCompanyId, ProductId, Name, Price)
		VALUES (S.ProductCompanyId, S.ProductId, S.Name, S.Price)
	OUTPUT S.ProductCompanyId, S.ProductId, Inserted.Id INTO @MergeProductsResults;


SELECT * FROM @SomeTable;