作者:張洪舉 Microsoft MVP
應用於:SQL Server 2008
日期:2008/9/1
6.MERGE 語句
在 SQL Server 2008 中,可以使用 MERGE 語句在一條語句中根據與源表聯接的結果對目標表執行 INSERT、UPDATE 或 DELETE 操作.如:使用一個語句有條件地在單個目標表中插入或更新行,如果目標表中存在相應行,則更新一個或多個列;否則,會將數據插入新行.使用該語句還可以同步兩個表,根據與源數據的差別在目標表中插入、更新或刪除行.
MERGE 語法包括如下五個主要子句:
MERGE 子句用於指定作為插入、更新或刪除操作目標的表或視圖.
USING 子句用於指定要與目標聯接的數據源.
ON 子句用於指定決定目標與源的匹配位置的聯接條件.
WHEN 子句用於根據 ON 子句的結果指定要執行的操作.
OUTPUT 子句針對更新、插入或刪除的目標對象中的每一行返回一行.
其完整的語法格式如下:
[ WITH
[,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias ]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]
[ OPTION ( [ ,...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
兩個表中的數據如下圖所示:
請注意,這兩個表中有兩個共有的產品-客戶行:客戶 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這樣的命名方式.
上面語句的輸出結果如下:
再查詢FactBuyingHabits表,可以看到被更新和插入后的結果,如下所示:
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)