建站
咨詢
售后
建站咨詢
新聞中心
此文章主要講述的是SQL Server 2008 Merge語(yǔ)句之功能，OUTPUT，SQL Server 2005的Output功能能把Insert，Update以及Delete的內(nèi)容全部都返回，2008數(shù)據(jù)庫(kù)中的Output同樣具有此功能，下面介紹一下把Output同2008的新T-SQL語(yǔ)句Merge組合使用的方法：
創(chuàng)新互聯(lián)專(zhuān)注于丹寨企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,商城網(wǎng)站定制開(kāi)發(fā)。丹寨網(wǎng)站建設(shè)公司,為丹寨等地區(qū)提供建站服務(wù)。全流程按需求定制開(kāi)發(fā)，專(zhuān)業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專(zhuān)業(yè)和態(tài)度為您提供的服務(wù)
SQL Server 2005中的Output功能可以把Insert，Update和Delete的內(nèi)容都返回，2008中的Output同樣具有此功能，下面介紹一下把Output同2008的新T-SQL語(yǔ)句Merge組合使用的方法：
新建下面表：
- view plaincopy to clipboardprint?
- CREATE TABLE Book(
- ISBN varchar(20) PRIMARY KEY,
- Price decimal,
- Shelf int)
- CREATE TABLE WeeklyChange(
- ISBN varchar(20) 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)
- CREATE TABLE Book(
- ISBN varchar(20) PRIMARY KEY,
- Price decimal,
- Shelf int)
- CREATE TABLE WeeklyChange(
- ISBN varchar(20) 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 Server 2008 Merge語(yǔ)句的OUTPUT功能的SQL語(yǔ)句為
- view plaincopy to clipboardprint?
- 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;
- 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;
結(jié)果集為：
- SELECT * FROM BookHistory
- GO
- Action NewISBN NewPrice NewShelf OldISBN OldPrice OldShelf ArchivedAt
- UPDATE A 101 1 A 100 1 2007-11-25 14:47:23.9907552
- INSERT C 300 3 NULL NULL NULL 2007-11-25 14:47:23.9907552
這里有Insert和Update兩種Output情況。如果只需要其中一種，可以用下面這種方法過(guò)濾：
- view plaincopy to clipboardprint?
- 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';
以上的相關(guān)內(nèi)容就是對(duì)SQL Server 2008 Merge語(yǔ)句的OUTPUT功能的介紹，望你能有所收獲。
網(wǎng)站標(biāo)題：SQLServer2008Merge語(yǔ)句之功能，OUTPUT
文章位置：http://www.5511xx.com/article/djjpdhc.html
