merge into和xml.modify 实例演示

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