sql 2008 Study for three -----Merge

1.语法

 MERGE <tatget table> [AS <alias>]

USING 

(

<source query>

)

WHEN  {[NOT] MATCHED | <expression> THEN <action statement>  [<additional WHEN  clauses>,[.....n]}

2.实例 参考sqlserver2008 高级编程

创建一张累计销售表

create table sales.monthyrollup

(

year smallest not null,

month tinyint not null,

productid int not null foreign key references procudtion.product(productid),

qtysold int not null constraint pkyearmonthproductid primary key(year,month,product)

);

--查询当月第一天的数据

MERGE sales.monthlyrollup as smr

(

select soh.orderdate,sod.productid,sum(sod.orderqty)as qtysold from sales.salesorderheader soh

join sales.salesorderdetail sod on soh.salesorderid=sod.salesorderid

where soh.orderdate>='2003-08-01' and son.orderdate<'2003-08-02' 

group by soh.orderdate,sod,productid

)  as s

on (s.productid=smr.productid)--这里的on可以理解为if

when matched then 

update set smr.qtysold =smr.qtysold+s.qtysold 

when not matched then

insert (year,month,productid,qtysold) values(date part(yy,s.orderdate),date part(m,s.orderdate),s.productid,s.qtysold);

--查询当月第二天的数据

MERGE sales.monthlyrollup as smr

(

select soh.orderdate,sod.productid,sum(sod.orderqty)as qtysold from sales.salesorderheader soh

join sales.salesorderdetail sod on soh.salesorderid=sod.salesorderid

where soh.orderdate>='2003-08-02' and son.orderdate<'2003-08-03' 

group by soh.orderdate,sod,productid

)  as s

on (s.productid=smr.productid)

when matched then 

update set smr.qtysold =smr.qtysold+s.qtysold 

when not matched then

insert (year,month,productid,qtysold) values(date part(yy,s.orderdate),date part(m,s.orderdate),s.productid,s.qtysold);

会发现数据没有新增那么多，只是将在第一天没有的productid新增到里面，而有的产品id会直接累加。

这里可以新建作业，写成存储过程，每天晚上去更新表，第二天早上就可以得到所需的数据。