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 

        public int UpdateShipDetailPacking(string customer_id, string prod, string oqc_inspect_no, string sales_order)
        {
            string upstr = $@"
                            --补盒号
                            UPDATE T SET T.PACKING_NO=LT.PACKING_NO
                            FROM    W_D_SHIP_DETAIL T
                                    INNER JOIN W_D_PACKING_LIST_ITEM LI ON LI.ITEM_NO = T.SERIAL_NO
                                    INNER JOIN W_D_PACKING_LIST LT ON LT.PROD = T.PROD
                                                                      AND LT.FACILITY = 'APAT'
                                                                      AND LT.SITE = 'SZ'
                                                                      AND LT.PACKING_LEVEL = 2  --盒号
                                                                      --AND LT.PACKING_LEVEL IN (1, 2, 3 )
                                                                      AND CHARINDEX(','+ CONVERT(VARCHAR(20), LT.ID) + ',', LI.TREE_CODE) > 0
                                                                      AND (T.PACKING_NO IS NULL OR T.PACKING_NO <> LT.PACKING_NO)
                            WHERE  T.FACILITY = 'APAT' AND T.SITE = 'SZ'
                                   { (customer_id == null ? "" : $" AND T.CUSTOMER_ID = '{customer_id}'")}
                                   { (prod == null ? "" : $" AND T.PROD = '{prod}'")}
                                   { (oqc_inspect_no == null ? "" : $" AND T.OQC_INSPECT_NO = '{oqc_inspect_no}'")}
                                   { (sales_order == null ? "" : $" AND T.SALES_ORDER = '{sales_order}'")}";
            return m_SqlDataAccess.ExecuteNonQuery(upstr);
        }