4年前 (2014-12-08)  SQL Server |   抢沙发  96 
文章评分 0 次,平均分 0.0

merge into和xml.modify 实例演示

CREATE TABLE [dbo].[t_statsXML](
  [f_id] [int] IDENTITY(1,1) NOT NULL,
  [f_gType] [tinyint] NULL,
  [f_kType] [tinyint] NULL,
  [f_bType] [int] NULL,
  [f_agAmt]  NULL,
  [f_cpAmt] [decimal](16, 2) NULL,
 PRIMARY KEY CLUSTERED 
 (
  [f_id] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];




DECLARE @gType INT=12

DECLARE @iAgId INT=1

DECLARE @tStats2    TABLE(f_kType TINYINT, f_bType INT PRIMARY KEY, f_cpAmt DECIMAL(16,2), f_agAmt DECIMAL(16,2)

INSERT INTO @tStats2(f_kType, f_bType, f_cpAmt, f_agAmt)

select 135,1111,100,10


         MERGE INTO t_statsXML AS T
         USING (SELECT f_kType, f_bType, f_cpAmt, f_agAmt FROM @tStats2) AS S
               (f_kType, f_bType, f_cpAmt, f_agAmt)
         ON T.f_gType = @gType AND T.f_kType = S.f_kType AND T.f_bType = S.f_bType
         WHEN MATCHED AND T.f_agAmt.exist('row[@id=sql:variable("@iAgId")]')=1  THEN
             UPDATE SET f_cpAmt += S.f_cpAmt, 
                 f_agAmt.modify('replace value of (row[@id=sql:variable("@iAgId")]/@f_amount)[1] with (sql:column("S.f_agAmt")+(row[@id=sql:variable("@iAgId")]/@f_amount)[1]) ')
         WHEN NOT MATCHED THEN
             INSERT (f_gType, f_kType, f_bType, f_cpAmt, f_agAmt)
             VALUES (@gType, S.f_kType, S.f_bType, S.f_cpAmt,(SELECT @iAgId AS id, S.f_agAmt AS f_amount FOR XML RAW));<span><span style="font-size:12px;line-height:18px;"> </span></span>




//下面这个css和插件后台设置的主题有关系,如果需要换样式,则需要修改以下CSS名称

 

除特别注明外,本站所有文章均为铁匠运维网原创,转载请注明出处来自http://www.tiejiang.org/2183.html

关于

发表评论

暂无评论

切换注册

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

切换登录

注册

扫一扫二维码分享