T-SQL 查询之增删改

• WITH ( )
  指定目标表允许的一个或多个表提示。需要有 WITH 关键字和括号。不允许使用 NOLOCK 和 READUNCOMMITTED。
• 当引用 Unicode 字符数据类型 nchar、nvarchar 和 ntext 时，“expression”应采用大写字母“N”作为前缀。如果未指定“N”，则 SQL Server 会将字符串转换为与数据库或列的默认排序规则相对应的代码页。

UPDATE  B
SET     B.ClassModuleID = A.ClassModuleID ,
        B.Summarize = A.Summarize
FROM    TepmB AS B
        INNER JOIN TepmA AS A WITH ( NOLOCK ) ON B.ModuleID = A.ModuleID
                                                 AND B.UserID = A.UserID
                                                 AND B.Authority = 1
                                                 AND B.Authority = 1

INSERT  TepmB
        SELECT  ISNULL(A.ClassModuleID, B.ClassModuleID) ,
                ISNULL(A.UserID, B.UserID) ,
                ISNULL(A.ModuleName, B.ModuleName) ,
                ISNULL(A.Moduledate, B.Moduledate) ,
                ISNULL(A.Summarize, B.Summarize) ,
                ISNULL(A.Modulexiangqing, B.Modulexiangqing) ,
                ISNULL(A.Images, B.Images) ,
                ISNULL(A.Linke, B.Linke) ,
                ISNULL(A.Authority, B.Authority)
        FROM    TepmB AS B
                INNER JOIN TepmA AS A WITH ( NOLOCK ) ON B.ModuleID = A.ModuleID
                                                         AND B.UserID = A.UserID
                                                         AND B.Authority = 1
                                                         AND B.Authority = 1 
或者
INSERT  TepmB  (VALUES1，VALUES2，VALUES3) --指定列
        SELECT  ISNULL(A.ClassModuleID, B.ClassModuleID)  AS  VALUES1,
                ISNULL(A.UserID, B.UserID) AS  VALUES2,
                ISNULL(A.ModuleName, B.ModuleName)  AS  VALUES3,
        FROM    TepmB AS B
                INNER JOIN TepmA AS A WITH ( NOLOCK ) ON B.ModuleID = A.ModuleID
                                                         AND B.UserID = A.UserID
                                                         AND B.Authority = 1
                                                         AND B.Authority = 1 

DELETE  B
FROM    TepmB AS B
        INNER JOIN TepmA AS A WITH ( NOLOCK ) ON B.ModuleID = A.ModuleID
                                                 AND B.UserID = A.UserID
                                                 AND B.Authority = 1
                                                 AND B.Authority = 1