SQL Example

MERGE.sql

DECLARE @candy TABLE (buydate SMALLDATETIME, name VARCHAR(10), piece INT)
INSERT @candy VALUES ('2014-09-01', 'candy1', 10)
INSERT @candy VALUES ('2014-09-01', 'candy2', 20)
INSERT @candy VALUES ('2014-10-01', 'candy2', 40)
INSERT @candy VALUES ('2014-10-01', 'candy3', 80)

DECLARE @delta TABLE (name VARCHAR(10), sep_count INT, oct_count INT)
INSERT @delta
SELECT name, piece, 0 FROM @candy WHERE buydate = '2014-09-01'

MERGE INTO @delta AS d
USING (SELECT * FROM @candy WHERE buydate = '2014-10-01') AS c
ON d.name = c.name
WHEN MATCHED THEN
    UPDATE SET d.oct_count = c.piece
WHEN NOT MATCHED THEN
    INSERT VALUES(c.name, 0,  c.piece);
	
SELECT * FROM @delta