作者：張洪舉 Microsoft MVP <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

應用于： SQL Server 2008

日期： 2008/9/1

6 ． MERGE 語句

在 SQL Server 2008 中，可以使用 MERGE 語句在一條語句中根據與源表聯接的結果對目標表執行 INSERT 、 UPDATE 或 DELETE 操作。如：使用一個語句有條件地在單個目標表中插入或更新行，如果目標表中存在相應行，則更新一個或多個列；否則，會將數據插入新行。使用該語句還可以同步兩個表，根據與源數據的差別在目標表中插入、更新或刪除行。

MERGE 語法包括如下五個主要子句：

MERGE 子句用于指定作為插入、更新或刪除操作目標的表或視圖。

USING 子句用于指定要與目標聯接的數據源。

ON 子句用于指定決定目標與源的匹配位置的聯接條件。

WHEN 子句用于根據 ON 子句的結果指定要執行的操作。

OUTPUT 子句針對更新、插入或刪除的目標對象中的每一行返回一行。

其完整的語法格式如下：

[ WITH <common_table_expression> [,...n] ]

MERGE

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

USING <table_source>

ON <merge_search_condition>

[ WHEN MATCHED [ AND <clause_search_condition> ]

THEN <merge_matched> ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

THEN <merge_not_matched> ]

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

THEN <merge_matched> ]

[ <output_clause> ]

[ OPTION ( <query_hint> [ ,...n ] ) ]

使用下面的語句創建兩個表：

USE AdventureWorks;

GO

IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL

DROP TABLE dbo.Purchases;

GO

CREATE TABLE dbo.Purchases (

ProductID int, CustomerID int, PurchaseDate datetime,

CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));

GO

INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),

(707, 15160, '20060825'),(708, 18529, '20060821'),

(712, 19072, '20060821'),(870, 15160, '20060823'),

(870, 11927, '20060824'),(870, 18749, '20060825');

GO

IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL

DROP TABLE dbo.FactBuyingHabits;

GO

CREATE TABLE dbo.FactBuyingHabits (

ProductID int, CustomerID int, LastPurchaseDate datetime,

CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));

GO

INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),

(707, 18178, '20060818'),(864, 14114, '20060818'),

(870, 17151, '20060818'),(870, 15160, '20060817'),

(871, 21717, '20060817'),(871, 21163, '20060815'),

(871, 13350, '20060815'),(873, 23381, '20060815');

GO

兩個表中的數據如下圖所示：

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="圖片_x0020_4" style="VISIBILITY: visible; WIDTH: 318pt; HEIGHT: 108pt; mso-wrap-style: square" alt="7.jpg" type="#_x0000_t75" o:spid="_x0000_i1027"><imagedata o:title="7" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image001.jpg"></imagedata></shape>

請注意，這兩個表中有兩個共有的產品 - 客戶行：客戶 11794 購買了產品 707 ，客戶 15160 購買了產品 870 。對于這些行，可以使用 WHEN MATCHED THEN 子句利用 Purchases 中這些購買記錄的日期來更新 FactBuyingHabits 。我們可以使用 WHEN NOT MATCHED THEN 子句將所有其他行插入 FactBuyingHabits 。參考下面的語句：

MERGE dbo.FactBuyingHabits AS Target

USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source

ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)

WHEN MATCHED THEN

UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate

WHEN NOT MATCHED BY TARGET THEN

INSERT (CustomerID, ProductID, LastPurchaseDate)

VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)

OUTPUT $action, Inserted.*, Deleted.*;

$action 用于在 OUTPUT 子句中指定一個 nvarchar(10) 類型的列，列的值是代表所執行操作的 INSERT 、 UPDATE 或 DELETE 。 Inserted.* 和 Deleted.* 分別用于指定返回所有插入行的列和刪除行的列。如果要指定具體的列，可以使用 Inserted.ProductID 這樣的命名方式。

上面語句的輸出結果如下：

<shape id="圖片_x0020_5" style="VISIBILITY: visible; WIDTH: 318.75pt; HEIGHT: 78pt; mso-wrap-style: square" alt="8.jpg" type="#_x0000_t75" o:spid="_x0000_i1026"><imagedata o:title="8" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image002.jpg"><font color="#000000" size="3"></font></imagedata></shape>

再查詢 FactBuyingHabits 表，可以看到被更新和插入后的結果，如下所示：

<shape id="圖片_x0020_6" style="VISIBILITY: visible; WIDTH: 153.75pt; HEIGHT: 2in; mso-wrap-style: square" alt="9.jpg" type="#_x0000_t75" o:spid="_x0000_i1025"><imagedata o:title="9" src="file:///C:%5CUsers%5Czhj%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image003.jpg"><font color="#000000" size="3"></font></imagedata></shape>

7 ． SQL 依賴關系報告

SQL Server 2008 引入了新的目錄視圖和系統函數用以提供一致可靠的 SQL 依賴關系報告。所謂依賴關系，通俗的講：存儲過程 1 需要使用存儲過程 2 提供的結果，它們之間就是一種依賴關系。可以使用 sys.sql_expression_dependencies 、 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 來報告架構綁定和非架構綁定對象的跨服務器、跨數據庫和數據庫 SQL 依賴關系。

下例將創建一個表、一個視圖和三個存儲過程。這些對象將用在后面的查詢中以演示如何報告依賴關系信息。可看到 MyView 和 MyProc3 均引用 Mytable 。 MyProc1 引用 MyView ，而 MyProc2 引用 MyProc1 。

USE AdventureWorks;

GO

-- Create entities

CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));

GO

CREATE VIEW dbo.MyView

AS SELECT c1, c2 FROM dbo.MyTable;

GO

CREATE PROC dbo.MyProc1

AS SELECT c1 FROM dbo.MyView;

GO

CREATE PROC dbo.MyProc2

AS EXEC dbo.MyProc1;

GO

CREATE PROC dbo.MyProc3

AS SELECT * FROM AdventureWorks.dbo.MyTable;

EXEC dbo.MyProc2;

GO

下面的示例查詢 sys.sql_expression_dependencies 目錄視圖以返回由 MyProc3 引用的實體。

USE AdventureWorks;

GO

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name

,referenced_server_name AS server_name

,referenced_database_name AS database_name

,referenced_schema_name AS schema_name

, referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');

GO

下面是結果集：

referencing_entity server_name database_name schema_name referenced_entity

------------------ ----------- ------------- ----------- -- ---------------

MyProc3 NULL NULL dbo MyProc2

MyProc3 NULL AdventureWorks dbo MyTable

上面的查詢返回了兩個在 MyProc3 定義中按名稱引用的實體。服務器名稱為 NULL ，因為被引用實體沒有使用有效的由四部分組成的名稱指定。返回的結果中顯示了 MyTable 的數據庫名稱，因為在存儲過程中是使用由三部分組成的有效名稱定義此實體的。

8 ．表值參數

數據庫引擎引入了可以引用用戶定義表類型的新參數類型。表值參數可以將多個數據行發送到 SQL Server 語句或例程（比如存儲過程或函數），而不用創建臨時表。表值參數具有更高的靈活性，在某些情況下，可比臨時表或其他傳遞參數列表的方法提供更好的性能。表值參數具有以下優勢：

首次從客戶端填充數據時，不獲取鎖。

提供簡單的編程模型。

允許在單個例程中包括復雜的業務邏輯。

減少到服務器的往返。

可以具有不同基數的表結構。

是強類型。

使客戶端可以指定排序順序和唯一鍵。

與其他參數一樣，表值參數的作用域也是存儲過程、函數或動態 Transact-SQL 文本。同樣，表類型變量也與使用 DECLARE 語句創建的其他任何局部變量一樣具有作用域。

與 BULK INSERT 操作相比，頻繁使用表值參數將比大型數據集要快。大容量操作的啟動開銷比表值參數大，與之相比，表值參數在插入數目少于 1000 的行時具有很好的執行性能。

下面是 SQL Server 幫助中的示例，演示了如何執行以下操作：創建表值參數類型，聲明變量來引用它，填充參數列表，然后將值傳遞到存儲過程。

USE AdventureWorks;

GO

/* 創建一個 table 類型 */

CREATE TYPE LocationTableType AS TABLE

( LocationName VARCHAR(50)

, CostRate INT );

GO

/* 創建一個存儲過程，用于從表值參數接收數據 */

CREATE PROCEDURE usp_InsertProductionLocation

@TVP LocationTableType READONLY

AS

SET NOCOUNT ON

INSERT INTO [AdventureWorks].[Production].[Location]

([Name]

,[CostRate]

,[Availability]

,[ModifiedDate])

SELECT *, 0, GETDATE()

FROM @TVP;

GO

/* 定義一個引用表值類型的變量 */

DECLARE @LocationTVP

AS LocationTableType;

/* 添加數據到表值變量 */

INSERT INTO @LocationTVP (LocationName, CostRate)

SELECT [Name], 0.00

FROM

[AdventureWorks].[Person].[StateProvince];

/* 傳遞表值變量數據給存儲過程 */

EXEC usp_InsertProductionLocation @LocationTVP;

GO

9 ． Transact-SQL 行構造函數

增強后的 Transact-SQL 可以允許將多個值插入單個 INSERT 語句中，語法比較簡單。參考下面的代碼：

/* 創建一個表 */

CREATE TABLE dbo.T1(

CustName char(20) ,

ProductID int ,

MadeFrom char(20) ,

Sales numeric(20, 2)

)

/* 插入 2 行數據 */

INSERT INTO dbo.T1

VALUES ('Jane',1,'China',20.00),

('Jack',2,'USA',10.00)

SQL Server 2008 的 Transact-SQL 語言增強（2）