SQL Server 2008 MERGE语法

liyihongcug

根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。
http://space.itpub.net/?uid-1643 ... space-itemid-515891
语法

[ 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 ] ) ]   
;

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::=
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
            [ WITH ( table_hint [ [ , ]...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
            [ ( bulk_column_alias [ ,...n ] ) ]
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML <openxml_clause>
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | <joined_table>
    | <pivoted_table>
    | <unpivoted_table>
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
    { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                                              | field_name = expression }
                                              | method_name ( argument [ ,...n ] ) }
     }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ]

<merge_not_matched>::=
{
        INSERT [ ( column_list ) ]
            { VALUES ( values_list )
            | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) }
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]

<predicate> ::=
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
    | string_expression [ NOT ] LIKE string_expression
  [ ESCAPE 'escape_character' ]
    | expression [ NOT ] BETWEEN expression AND expression
    | expression IS [ NOT ] NULL
    | CONTAINS
    ( { column | * } , '< contains_search_condition >' )
    | FREETEXT ( { column | * } , 'freetext_string' )
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] )
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
  { ALL | SOME | ANY} ( subquery )
    | EXISTS ( subquery ) }

<output_clause>::=
{
    [ OUTPUT <dml_select_list> ]
        [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
            [ (column_list) ] ]
}

<dml_select_list>::=
        { <column_name> | scalar_expression }
                [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
        { DELETED | INSERTED | from_table_name } . { * | column_name }
      | $action
参数
WITH <common_table_expression>
指定在 MERGE 语句作用域内定义的临时命名结果集或视图，也称为公用表表达式。该结果集派生自一个简单查询，并由 MERGE 语句引用。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

TOP ( expression ) [ PERCENT ]
指定受影响的行数或行百分比。expression 可以为行数，也可以为行百分比。在 TOP 表达式中引用的行不是以任意顺序排列的。有关详细信息，请参阅 TOP (Transact-SQL)。

在联接整个源表和整个目标表并且删除了不符合插入、更新或删除操作条件的联接行之后，应用 TOP 子句。TOP 子句将联接行的数量进一步减少为指定值，并且以一种无序方式对其余联接行应用插入、更新或删除操作。也就是说，在 WHEN 子句中定义的操作中，这些行是无序分布的。例如，如果指定 TOP (10)，将会影响 10 行；在这些行中，可能会更新 7 行而插入 3 行，或者可能删除 1 行、更新 5 行并且插入 4 行，等等。

由于 MERGE 语句对源表和目标表都进行完全表扫描，因此在使用 TOP 子句通过创建多个批处理来修改大型表时，I/O 性能可能会受到影响。在这种情况下，一定要确保所有连续批处理都以新行作为处理目标。有关详细信息，请参阅优化 MERGE 语句性能。

target_table
表或视图，<table_source> 中的数据行将根据 <clause_search_condition> 与该表或视图进行匹配。target_table 是由 MERGE 语句的 WHEN 子句指定的任何插入、更新或删除操作的目标。

如果 target_table 为视图，则针对它的任何操作都必须满足更新视图所需的条件。有关详细信息，请参阅通过视图修改数据。target_table 不能是远程表。target_table 不能具有针对它定义的任何规则。

[ AS ] table_alias
用于引用表的替代名称。

USING <table_source>
指定基于 <merge_search_condition> 与 target_table 中的数据行进行匹配的数据源。此匹配的结果指出了要由 MERGE 语句的 WHEN 子句采取的操作。<table_source> 可以是一个远程表，或者是一个能够访问远程表的派生表。

<table_source> 可以是一个使用 Transact-SQL 行构造函数功能在单个语句中指定多个行的派生表。

有关此子句的语法和参数的详细信息，请参阅 FROM (Transact-SQL)。

ON <merge_search_condition>
指定在 <table_source> 与 target_table 进行联接以确定它们的匹配位置时所遵循的条件。

注意：
重要的是，应仅指定目标表中用于匹配目的的列。也就是说，指定与源表中的对应列进行比较的目标表列。不要尝试通过在 ON 子句中筛选掉目标表中的行来提高查询性能，例如，指定 AND NOT target_table.column_x = value。这样做可能会返回意外和不正确的结果。



WHEN MATCHED THEN <merge_matched>
指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行匹配并满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。MERGE 语句最多可以有两个 WHEN MATCHED 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <search_condition> 子句。对于任何给定的行，只有在未应用第一个 WHEN MATCHED 子句的情况下，才会应用第二个 WHEN MATCHED 子句。如果有两个 WHEN MATCHED 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。如果在 <merge_matched> 子句中指定了 UPDATE，并且根据 <merge_search_condition>，<table_source> 中的多个行与 target_table 中的某一行匹配，则 SQL Server 将返回错误。MERGE 语句无法多次更新同一行，也无法更新和删除同一行。

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
指定对于 <table_source> ON <merge_search_condition> 返回的每一行，如果该行与 target_table 中的行不匹配，但是满足其他搜索条件（如果存在），则在 target_table 中插入一行。要插入的值是由 <merge_not_matched> 子句指定的。MERGE 语句只能有一个 WHEN NOT MATCHED 子句。

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。

MERGE 语句最多可以有两个 WHEN NOT MATCHED BY SOURCE 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <clause_search_condition> 子句。对于任何给定的行，只有当未应用第一个 WHEN NOT MATCHED BY SOURCE 子句时才会应用第二个子句。如果有两个 WHEN NOT MATCHED BY SOURCE 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。在 <clause_search_condition> 中只能引用目标表中的列。

当 <table_source> 未返回任何行时，无法访问源表中的列。如果 <merge_matched> 子句中指定的更新或删除操作引用了源表中的列，将返回错误 207（无效列名）。例如，由于无法访问源表中的 Col1，因此 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 子句可能导致该语句失败。

AND <clause_search_condition>
指定任何有效的搜索条件。有关详细信息，请参阅搜索条件 (Transact-SQL)。

<table_hint_limited>
为由 MERGE 语句执行的每个插入、更新或删除操作指定对目标表应用的一个或多个表提示。需要有 WITH 关键字和括号。

不允许使用 NOLOCK 和 READUNCOMMITTED。有关表提示的详细信息，请参阅表提示 (Transact-SQL)。

对作为 INSERT 语句目标的表指定 TABLOCK 提示与指定 TABLOCKX 提示具有相同的效果。对表采用排他锁。如果指定了 FORCESEEK，会将其应用于与源表联接的目标表的隐式实例。

注意：
指定带有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能会导致违反 UNIQUE 约束的 INSERT 操作。



INDEX ( index_val [，...n ] )
指定目标表上的一个或多个索引的名称或 ID，以执行与源表的隐式联接。有关详细信息，请参阅表提示 (Transact-SQL)。

<output_clause>
不按照任何特定顺序为 target_table 中更新、插入或删除的每一行返回一行。有关该子句的参数的详细信息，请参阅 OUTPUT 子句 (Transact-SQL)。

$action
在 OUTPUT 子句中指定一个 nvarchar(10) 类型的列，该子句为每一行返回以下三个值之一：'INSERT'、'UPDATE' 或 'DELETE'，具体返回其中哪个值取决于对该行执行的操作。

如果数据库排序规则区分大小写，则 $action 必须小写。

OPTION ( <query_hint> [ ,...n ] )
指定使用优化器提示来自定义数据库引擎处理语句的方式。有关详细信息，请参阅查询提示 (Transact-SQL)。

<merge_matched>
指定更新或删除操作，这些操作应用于 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行。

UPDATE SET <set_clause>
指定目标表中要更新的列或变量名的列表，以及用于更新它们的值。

有关该子句的参数的详细信息，请参阅 UPDATE (Transact-SQL)。不允许将变量设置为与列相同的值。

DELETE
指定删除与 target_table 中的行匹配的行。


<merge_not_matched>
指定要插入到目标表中的值。

( column_list )
要在其中插入数据的目标表中的一列或多列的列表。必须使用单一部分名称格式来指定这些列，否则 MERGE 语句将失败。column_list 必须用圆括号括起来，并用逗号进行分隔。

VALUES ( values_list )
一个逗号分隔列表，其中包含常量、变量或者返回要插入到目标表中的值的表达式。表达式不能包含 EXECUTE 语句。

DEFAULT VALUES
强制插入的行包含为每个列定义的默认值。

有关该子句的详细信息，请参阅 INSERT (Transact-SQL)。

<search condition>
指定用于指定 <merge_search_condition> 或 <clause_search_condition> 的搜索条件。有关该子句的参数的详细信息，请参阅搜索条件 (Transact-SQL)。

备注
必须指定三个 MATCHED 子句中的至少一个子句，但可以按任何顺序指定。不能在同一个 MATCHED 子句中多次更新一个变量。

由 MERGE 语句指定的目标表中的任何插入、更新或删除操作都受为它定义的任何约束的限制，包括任何级联引用完整性约束。如果 IGNORE_DUP_KEY 对于目标表中的任何唯一索引都设置为 ON，则 MERGE 将忽略此设置。

MERGE 语句需要一个分号 ( 作为语句终止符。如果运行没有终止符的 MERGE 语句，将引发错误 10713。

如果在 MERGE 之后使用，@@ROWCOUNT (Transact-SQL) 会返回为客户端插入、更新和删除的行的总数。

在数据库兼容级别设置为 100 的情况下，MERGE 是完全保留的关键字。MERGE 语句在数据库兼容级别为 90 和 100 的情况下也可用；但在数据库兼容级别设置为 90 时，此关键字不是完全保留的关键字。

触发器的实现
对于在 MERGE 语句中指定的每个插入、更新或删除操作，SQL Server 都会激发针对目标表定义的任何对应的 AFTER 触发器，但不保证哪个操作最先或最后激发触发器。为相同操作定义的触发器会遵循您指定的顺序进行触发。有关设置触发器激发顺序的详细信息，请参阅指定第一个和最后一个触发器。

对于由 MERGE 语句执行的插入、更新或删除操作，如果目标表具有针对自己定义的已启用 INSTEAD OF 触发器，那么对于在 MERGE 语句中指定的所有操作，它都必须具有已启用的 INSTEAD OF 触发器。

如果对 target_table 定义了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 触发器，则不会执行更新或删除操作，而是会激发触发器并相应地填充 inserted 和 deleted 表。

如果对 target_table 定义了任何 INSTEAD OF INSERT 触发器，则不会执行插入操作，而是会激发触发器并相应地填充 inserted 表。

权限
需要对源表的 SELECT 权限和对目标表的 INSERT、UPDATE 或 DELETE 权限。有关其他信息，请参阅 SELECT、INSERT、UPDATE 和 DELETE 主题中的“权限”部分。

示例
A. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作
下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数，更新 ProductInventory 表的 Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少，则会从 ProductInventory 表中删除该产品对应的行。


USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501'
B. 借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作
下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时，就会更新此目标表中的 ReasonType 列。当 NewName 的值不匹配时，就会将源行插入到目标表中。此源表是一个派生表，它使用 Transact-SQL 行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息，请参阅 FROM (Transact-SQL)。


USE AdventureWorks;
GO
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action, inserted.*, deleted.*;
C. 将 MERGE 语句的执行结果插入到另一个表中
下例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将该数据插入另一个表。MERGE 语句根据在 SalesOrderDetail 表中处理的订单，更新 ProductInventory 表的 Quantity 列。本示例捕获已更新的行，并将这些行插入用于跟踪库存变化的另一个表中。


USE AdventureWorks;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO

有关其他示例，请参阅使用 MERGE 插入、更新和删除数据和优化 MERGE 语句性能。

liyihongcug

Server 存储过程简介与使用方法

      Sql Server的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。

      存储过程相对于其他的数据库访问方法有以下的优点：

            （1）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

            （2）提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。

            （3）减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

            （4）安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

      存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。

      其中，用户定义的存储过程又分为Transaction-SQL和CLR两种类型。

      Transaction-SQL 存储过程是指保存的Transaction-SQL语句集合，可以接受和返回用户提供的参数。

      CLR存储过程是指对.Net Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。他们在.Net Framework程序集中是作为类的公共静态方法实现的。（本文就不作介绍了）

      创建存储过程的语句如下：
Code
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ [ OUT [ PUT ]
    ] [ ,n ]
[ WITH <procedure_option> [ ,n ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ n ] | <method_specifier> }
[;]
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name



      [schema_name]: 代表的是存储过程所属的架构的名称

      例如：

            Create Schema yangyang8848      
            Go
            Create Proc yangyang8848.AllGoods
            As Select * From Master_Goods
            Go

            执行：Exec AllGoods 发生错误。

            执行：Exec yangyang8848.AllGoods 正确执行。

      [；Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

      例如：

            Create Proc S1 ;1
            AS
                   Select * From Master_Goods
            Go
            Create Proc S1 ;2
            As
                   Select * From Master_Location
            Go

            创建完毕了两个存储过程。它们在同一个组S1里，如果执行Exec S1 则存储过程默认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行Exec S1 ;2。当我们要删除存储过程的时候，只能执行Drop Exec S1 则该组内所有的存储过程被删除。

      [@ parameter]: 存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。

      存储过程最多有2100个参数。

      例如：

      Create Proc yangyang8848.OneGoods
      @GoodsCode varchar(10)
      As
             Select * From Master_Goods Where GoodsCode = @GoodsCode
      Go

      调用的代码：

      Declare @Code varchar(10)
      Set @Code = '0004'
      Exec yangyang8848.OneGoods @Code

      在参数的后边加入Output 表明该参数为输出参数。

      Create Proc yangyang8848.OneGoods
      @GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011'
      As
             Select * From Master_Goods Where GoodsCode = @GoodsCode
             Set @GoodsCode2 = '0005'
      Go

      调用方法：
      Declare @VV2 varchar(10)
      Exec yangyang8848.OneGoods  @Code out

      注意：如果存储过程的两个参数一个有默认值一个没有，那么我们要把有默认值得放在后边，不然会出问题哦~~

      细心的朋友，可能看到上边的语句有一些不同，比如，存储过程用的是output，而调用语句用的是out。我要告诉您，两者是一样的。

      
      [RECOMPILE]：指示数据库引擎 不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定 RECOMPILE。



      这个说一个非常好用的函数 OBJECT_ID ：返回架构范围内对象的数据库对象标识号。

      例如：我们创建存储过程时，可以如下写代码

      If Object_ID('yangyang8848.OneGoods') Is Not Null
             Drop Proc yangyang8848.OneGoods
      Go

      Create Proc yangyang8848.OneGoods
      @GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
      As
             Select * From Master_Goods Where GoodsCode = @GoodsCode
             Set @GoodsCode2 = '0005'
      Go

      针对于上边的这个存储过程，我们调用以下SQL查询

      Select definition From sys.sql_modules
            Where object_id = Object_ID('yangyang8848.OneGoods');

      我们是可以查到结果的。

      可是如果我们对该存储过程加入[ ENCRYPTION ] 那么你将无法看到任何结果

      If Object_ID('yangyang8848.OneGoods') Is Not Null
             Drop Proc yangyang8848.OneGoods
      Go

      Create Proc yangyang8848.OneGoods
      @GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'

      With Encryption
      As
             Select * From Master_Goods Where GoodsCode = @GoodsCode
             Set @GoodsCode2 = '0005'
      Go

      然后我们查询 sys.sql_modules 目录视图，将返回给你Null。

      然后我们执行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods'

      你将得到以下结果：The text for object 'yangyang8848.OneGoods' is encrypted.

      说到这里你应该明白了，参数[ ENCRYPTION ]：是一种加密的功能， 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。



      前两天写了一篇关于游标的介绍文章 ，下边写一个例子，将游标与存储过程一起使用上：

      If Object_ID('dbo.GetMasterGoods') Is Not Null
            Drop Proc dbo.GetMasterGoods
      Go

      Create Proc GetMasterGoods
      @MyCursor Cursor Varying Output
      With Encryption
      As
             Set @MyCursor = Cursor
             For
                    Select GoodsCode,GoodsName From Master_Goods
      Open @MyCursor
      Go

      --下边建立另外一个存储过程，用于遍历游标输出结果

      Create Proc GetAllGoodsIDAndName
      As

      Declare @GoodsCode varchar(1
      Declare @GoodsName nvarchar(20)
      Declare @MasterGoodsCursor Cursor
      Exec GetMasterGoods @MasterGoodsCursor out
      Fetch Next From @MasterGoodsCursor
      InTo @GoodsCode,@GoodsName
      While(@@Fetch_Status = 0)
      Begin
             Begin
                    Print @GoodsCode + ':' + @GoodsName
             End
             Fetch Next From @MasterGoodsCursor
             InTo @GoodsCode,@GoodsName
      End
      Close @MasterGoodsCursor
      Deallocate @MasterGoodsCursor
      Go

      最后执行Exec GetAllGoodsIDAndName结果为以下内容

      0003:品0003
      0004:品0004
      0005:123123
      0006:品0006
      0007:品0007
      0008:品0008
      0009:品0009
      0010:品0010
      0011:品0011
      0012:品0012
      0013:品0013
      0014:品0014


欢迎大家访问我的网站: http://www.shehui001.com/

liyihongcug

http://msftdbprodsamples.codeplex.com/releases/view/4004
SQL Server 2008的实用小道具——merger
http://database.51cto.com  2010-09-06 14:14  无为是最高  博客园  我要评论(0)

    * 摘要：作为SQL Server 2008中推出的一个小道具merger，具备插入，删除，修改一起来（适合数据的同步）的功能。让我们一起来认识它吧。
    * 标签：merger  SQL Server 2008
*

根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。

A. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作

下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数，更新 ProductInventory 表的 Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少，则会从 ProductInventory 表中删除该产品对应的行。

   1. USE AdventureWorks;  
   2. GO  
   3. IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P')
   4. IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;  
   5. GO  
   6. CREATE PROCEDURE Production.usp_UpdateInventory  
   7.     @OrderDate datetime  
   8. AS
   9. MERGE Production.ProductInventory AS target  
  10. USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
  11.     JOIN Sales.SalesOrderHeader AS soh  
  12.     ON sod.SalesOrderID = soh.SalesOrderID  
  13.     AND soh.OrderDate = @OrderDate  
  14.     GROUP BY ProductID) AS source (ProductID, OrderQty)  
  15. ON (target.ProductID = source.ProductID)  
  16. WHEN MATCHED AND target.Quantity - source.OrderQty <= 0  
  17.     THEN DELETE
  18. WHEN MATCHED  
  19.     THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,  
  20.                     target.ModifiedDate = GETDATE()  
  21. OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,  
  22.     Deleted.Quantity, Deleted.ModifiedDate;  
  23. GO  
  24.  
  25. EXECUTE Production.usp_UpdateInventory '20030501'

B. 借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作

下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时，就会更新此目标表中的 ReasonType 列。当 NewName 的值不匹配时，就会将源行插入到目标表中。此源表是一个派生表，它使用 Transact-SQL 行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息，请参阅 FROM (Transact-SQL)。

   1. USE AdventureWorks;  
   2. GO  
   3. MERGE INTO Sales.SalesReason AS Target  
   4. USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))  
   5.        AS Source (NewName, NewReasonType)  
   6. ON Target.Name = Source.NewName  
   7. WHEN MATCHED THEN
   8.  UPDATE SET ReasonType = Source.NewReasonType  
   9. WHEN NOT MATCHED BY TARGET THEN
  10.  INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
  11. OUTPUT $action, inserted.*, deleted.*;

C. 将 MERGE 语句的执行结果插入到另一个表中
下例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将该数据插入另一个表。MERGE 语句根据在 SalesOrderDetail 表中处理的订单，更新 ProductInventory 表的 Quantity 列。本示例捕获已更新的行，并将这些行插入用于跟踪库存变化的另一个表中。

   1. USE AdventureWorks;  
   2. GO  
   3. CREATE TABLE Production.UpdatedInventory  
   4.     (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,  
   5.      CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));  
   6. GO  
   7. INSERT INTO Production.UpdatedInventory  
   8. SELECT ProductID, LocationID, NewQty, PreviousQty  
   9. FROM
  10. (    MERGE Production.ProductInventory AS pi  
  11.      USING (SELECT ProductID, SUM(OrderQty)  
  12.             FROM Sales.SalesOrderDetail AS sod  
  13.             JOIN Sales.SalesOrderHeader AS soh  
  14.             ON sod.SalesOrderID = soh.SalesOrderID  
  15.             AND soh.OrderDate BETWEEN '20030701' AND '20030731'
  16.             GROUP BY ProductID) AS src (ProductID, OrderQty)  
  17.      ON pi.ProductID = src.ProductID  
  18.     WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0  
  19.         THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
  20.     WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
  21.         THEN DELETE
  22.     OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)  
  23.  AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';  
  24. GO

原文标题：SQL SERVER 2008的几个新东西：插入，删除，修改一起来（适合数据的同步）-----merger

链接： http://www.cnblogs.com/buaaboyi/archive/2010/09/05/1818281.html

liyihongcug

ql server 2005中output用法解析

一、关于output子句

SQL Server 2005中的output子句，可以使你从修改语句（INSERT、UPDATE、DELETE）中将数据返回到表变量中。带结果的 DML 的有用方案包括清除和存档、消息处理应用程序以及其他方案。这一新的 OUTPUT 子句的语法为：

OUTPUT INTO @table_variable



可以通过引用插入的表和删除的表来访问被修改的行的旧/新映像，其方式与访问触发器类似。在 INSERT 语句中，只能访问插入的表。在 DELETE 语句中，只能访问删除的表。在 UPDATE 语句中，可以访问插入的表和删除的表。

二、实例说明

1、将 OUTPUT INTO 用于简单 INSERT 语句

以下示例将行插入 ScrapReason 表，并使用 OUTPUT 子句将语句的结果返回到 @MyTableVar table 变量。由于 ScrapReasonID 列使用 IDENTITY 属性定义，因此未在 INSERT 语句中为该列指定一个值。但请注意，将在列 INSERTED.ScrapReasonID 内的 OUTPUT 子句中返回由数据库引擎 为该列生成的值。
代码

use AdventureWorks
go
--定义一个表格变量
declare @mytablevar table( ScrapReasonID smallint,
Name1 varchar(50),
ModifiedDate datetime)
insert into Production.ScrapReason
output inserted.ScrapReasonID,inserted.[Name],inserted.ModifiedDate into @mytablevar
values ('operator error',getdate());
--显示@mytablevar中的数据
select * from @mytablevar
--显示Production.ScrapReason表中的数据
select * from Production.ScrapReason
go



以上语句中inserted是一个临时表，当我们往数据表中插入一条数据的时候数据库会创建一个临时表inserted保存插入的记录；当我们删除某条记录的时候，数据库会创建一个临时表deleted保存删除的记录。以上语句把新插入的数据填充到表变量@mytablevar中，然后输出数据，可以看到@mytablevar中的记录和Production.ScrapReason中新插入的数据是一样的。

2、 将 OUTPUT 用于 INSERT…SELECT 语句

以下示例创建 EmployeeSales 表，然后通过使用 SELECT 语句检索源表中的数据将几行插入该表。同时，也计算了列 ProjectedSales 的值并将其插入该表中。OUTPUT 子句将 INSERT 语句的结果返回到执行调用的应用程序。最后的 SELECT 语句验证新 EmployeeSales 表的内容是否与 OUTPUT 子句的结果匹配。
代码

use AdventureWorks
go
if object_id('dbo.EmployeeSales','u') is not null
drop table dbo.EmployeeSales
go
create table dbo.EmployeeSales
(
EmployeeID nvarchar(11) not null,
LastName nvarchar(20) not null,
FirstName nvarchar(20) not null,
CurrentSales money not null,
ProjectedSales money not null

)
go
insert into dbo.EmployeeSales
output inserted.EmployeeID,inserted.LastName,inserted.FirstName,inserted.CurrentSales,inserted.ProjectedSales
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO



3、  将 OUTPUT 用于 DELETE 语句

以下示例将删除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。




USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;

--验证表中所有数据都被删除
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

4、 将 OUTPUT INTO 用于 UPDATE

下面的示例将 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句将返回 VacationHours 值，该值在将列 DELETED.VacationHours 中的 UPDATE 语句和列 INSERTED.VacationHours 中的已更新值应用于 @MyTableVar table 变量之前存在。
在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。请注意，INSERTED.ModifiedDate 列中的结果与 Employee 表中的 ModifiedDate 列不具有相同的值。这是因为对 Employee 表定义了将 ModifiedDate 的值更新为当前日期的 AFTER UPDATE 触发器。但是，从 OUTPUT 中返回的列反映触发器激发之前的数据。
代码

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--显示@MyTableVar的值
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--显示插入表的值
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

5、 使用 OUTPUT INTO 返回表达式

以下示例在示例 D 的基础上生成，方法是通过将 OUTPUT 子句中的表达式定义为已更新的 VacationHours 值与应用更新之前的 VacationHours 值之间的差异。该表达式的值返回到列 VacationHoursDifference 中的 @MyTableVar table 变量。
代码

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
VacationHoursDifference int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.VacationHours - DELETED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--显示表变量中的数据
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

  

6、 在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO

以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表来指定要更新的行。由于 WorkOrder 表具有对其定义的 AFTER UPDATE 触发器，因此需要 INTO 关键字。
代码

USE AdventureWorks;
GO
DECLARE @MyTestVar table (
OldScrapReasonID int NOT NULL,
NewScrapReasonID int NOT NULL,
WorkOrderID int NOT NULL,
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
INSERTED.ScrapReasonID,
INSERTED.WorkOrderID,
INSERTED.ProductID,
p.Name
INTO @MyTestVar
FROM Production.WorkOrder AS wo
INNER JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
AND wo.ScrapReasonID= 16
AND p.ProductID = 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
ProductID, ProductName
FROM @MyTestVar;
GO

7、在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTO

以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用该表来指定要删除的行。


代码

USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;

SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO



8、 将 OUTPUT INTO 用于大型对象数据类型s  

以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVar table 变量。请注意，将返回 DocumentSummary 列的全部前像和后像。

代码

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
DocumentID int NOT NULL,
SummaryBefore nvarchar(max),
SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
DELETED.DocumentSummary,
INSERTED.DocumentSummary
INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO

9、 在 INSTEAD OF 触发器中使用 OUTPUT

以下示例在触发器中使用 OUTPUT 子句返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。由于列 ScrapReasonID 是基表中的 IDENTITY 列，因此触发器将忽略用户提供的值。这允许数据库引擎 自动生成正确的值。同样，用户为 ModifiedDate 提供的值也被忽略并设置为正确的日期。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

代码

UUSE AdventureWorks;
GO
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL
DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS (SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason);
GO
CREATE TRIGGER dbo.io_ScrapReason
ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted
--because it is an IDENTITY column.
INSERT INTO Production.ScrapReason (Name, ModifiedDate)
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
INSERTED.ModifiedDate
SELECT Name, getdate()
FROM inserted;
END
GO
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
VALUES (99, N'My scrap reason','20030404');
GO



10、  将 OUTPUT INTO 用于标识列和计算列

下面的示例创建 EmployeeSales 表，然后使用 INSERT 语句向其中插入若干行，并使用 SELECT 语句从源表中检索数据。EmployeeSales 表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。由于这些值是在插入操作期间由数据库引擎生成的，因此，不能在 @MyTableVar 中定义上述两列。

代码