Here are a few ways to combine the new T-SQL statement merge of output with 2008:
Create a new table below:
Copy Code code as follows:
CREATE TABLE Book (
ISBN varchar () PRIMARY KEY,
Price Decimal,
Shelf int)
CREATE TABLE Weeklychange (
ISBN varchar () PRIMARY KEY,
Price Decimal,
Shelf int)
CREATE TABLE Bookhistory (
Action nvarchar (10),
NEWISBN varchar (20),
Newprice Decimal,
newshelf int,
OLDISBN varchar (20),
Oldprice Decimal,
oldshelf int,
Archivedat datetime2)
SQL statement is
Copy Code code as follows:
MERGE Book as B
USING Weeklychange as WC
On B.ISBN = WC. Isbn
When matched and (B.price <> WC. Price OR b.shelf <> WC. Shelf) THEN
UPDATE SET b.price = WC. Price, b.shelf = WC. Shelf
When not matched THEN
INSERT VALUES (WC. ISBN, WC. Price, WC. Shelf)
OUTPUT $action, inserted.*, deleted.*, Sysdatetime ()
into Bookhistory;
The result set is:
SELECT * from Bookhistory
Go
Action newisbn newprice newshelf oldisbn oldprice oldshelf
------ ------- -------- -------- ------- -------- -------- ---------------------------
UPDATE a 1 a 100 1 2007-11-25 14:47:23.9907552
INSERT C 3 null null 2007-11-25 14:47:23.9907552
There are insert and update two output scenarios. If you only need one of these, you can filter it in the following way:
Copy Code code as follows:
INSERT into book (ISBN, Price, shelf, archivedat)
SELECT ISBN, price, shelf, GETDATE () from
(MERGE book as B
USING Weeklychange as WC
On B.ISBN = WC. ISBN and B.archivedat is NULL
When matched and (B.price <> WC. Price OR b.shelf <> WC. Shelf) THEN
UPDATE SET price = WC. Price, shelf = WC. Shelf
When not matched THEN
INSERT VALUES (WC. ISBN, WC. Price, WC. Shelf, NULL)
OUTPUT $action, WC. ISBN, Deleted.price, deleted.shelf
) CHANGES (Action, ISBN, price, shelf)
WHERE Action = ' UPDATE ';
