ID Down Item shift1 shift2 shift3 shift4 week link
1 1M CONVEYOR 1 34 0 0 WW01_07 link1
3 UNLOADER 13 0 0 0 WW01_07 link1
4 Total Down Time 14 0 2 0 WW01_07 link1
6 Conversion Time 16 0 0 0 WW01_07 link1
7 Asssit1 17 0 5 0 WW01_07 link1
8 Asssit2 18 0 0 0 WW01_07 link1
9 IPAC 70 0 0 0 WW01_07 link2
10 LOADER 71 0 0 0 WW01_07 link2
11 UNLOADER 76 0 0 0 WW01_07 link2
12 Total Down Time 77 0 0 0 WW01_07 link2
15 Assist1 80 0 0 0 WW01_07 link2
16 Assist2 81 0 0 0 WW01_07 link2
17 Assist3 82 0 0 0 WW01_07 link2
18 Assist4 83 0 0 0 WW01_07 link2
问题描述：
已知上面数据是数据库DATA1的表sheet1的数据格式，该数据库是由其它系统实现更新的，怎么在SQL server中写SQL语句
实现数据的自动更新，每天07：00更新。

目标数据库data2,表table1的格式如下：
ID（自动编号） total time Asssit_time week link

该列等于DATA1，sheet1的down Item为 所有down Item like"Assist*”的四个shift之和
“Total Down Time"的4个shift之和。
若数据为上表那样，更新后该表数据为：
1 14+0+2+0=16 17+5+18=40 WW01_07 link1
2 77+0+0+0=77 326 WW01_07 link2

怎么写JOB语句实现更新呢?
谢谢！

写个存储过程 放到任务定时执行!

Create proc pr_AutoUpdate

as

use data1

select down_item,total_time = shift1+shift2+shitf3+shift4,week,link into #1 from sheet1 where down_item = 'Total Down Time'
--如果考虑到sheet1 每天更新前不会清理数据 那么每次只插入新数据 则select语句应该为
select down_item,total_time = shift1+shift2+shitf3+shift4,week,link into #1 from sheet1 where down_item = 'Total Down Time'
and week + link not in (select wk = week+link from data2..table1)

Insert into data2..table1(Total_time, Assist_Time,Week,Link)
select total_time,Assist_Time = sum(shift1)+sum(shift2)+sum(shift3)+sum(shift4),a.week,a.link
from #1 a join sheet1 b on a.week =b.week and a.link = b.link
and b.down_item like 'Assist%'
group by total_time,a.week,a.link

go

对这一类问题，最好用任务调度，也就是我们经常所说的Job，
到达时间点就会执行
一般用来数据库的维护如：数据库的备份