数据COPY问题~~

[qianduo]

Retrieve数据后，点击COPY按钮。则允许用户保存datawindow中当前存在的这条记录。
我现在的想法也就是这个COPY相当于新增一条记录，然后把以前retrieve的数据COPY给新增的这条数据。当用户再点击保存按钮后，数据再保存在数据库中.
请问大家有什么更好的解决方案没有？

[workhand]

点【增加】就insert一条，然后用户输入，点【保存】保存dw的内容；如果点【复制】新增一条，将刚才当前记录复制到新增的行上，等用户修改，点【保存】保存dw的内容。也就这两种呗

[lovelihp]

顶楼上的

[li_d_s]

用RowsCopy函数啊

[saiche05]

dd
ing

[qianduo]

create table record
(
emp_no varchar(10),
shift_type varchar(2),
T_date varchar(15),
T_time varchar(15)
)

insert record
select 'A1459','MS','2004.04.01','0744' union
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' union

select 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' union

select 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'

create table emp_mst
(
emp_no varchar(10),
shift_type varchar(2)
)
insert emp_mst
select 'A1459', 'MS' union
select 'A2176', 'LS'


create table shift_mst
(
SHIFT_TYPE varchar(10),
WORK_START varchar(10),
WORK_END varchar(15),
OVER_NIGHT varchar(15),
START_PUNCH varchar(15),
STOP_PUNCH varchar(15),
OT_START varchar(15),
OT_END varchar(15),
OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','21:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','Y'

go


CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME --日期范围截止日期

AS
BEGIN

declare @emp_id varchar(10), @wk_st varchar(10), @wk_ed varchar(10), @ov_night varchar(15), @ot_ov_night varchar(2)
declare @ls_shift varchar(2), @st_punch varchar(15), @end_punch varchar(15), @st_ot varchar(15), @end_ot varchar(15)

create table #att_detail(
emp_no varchar(10),
T_date varchar(15),
shift_type varchar(2),
work_start datetime,
work_end datetime,
punch_start datetime,
punch_end datetime,
ot_start datetime,
ot_end datetime,
on_duty datetime,
off_duty datetime

)

Declare cur_emp cursor for
select emp_mst.emp_no, emp_mst.shift_type, shift_mst.work_start,
shift_mst.work_end, shift_mst.over_night, shift_mst.start_punch, shift_mst.stop_punch, shift_mst.ot_start,
shift_mst.ot_end, shift_mst.ot_ove_night
from emp_mst, shift_mst where emp_mst.shift_type = shift_mst.shift_type


open cur_emp
fetch from cur_emp into @emp_id, @ls_shift, @wk_st,
@wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
@end_ot, @ot_ov_night

while @@fetch_status = 0
begin

insert #att_detail (emp_no, t_date, shift_type, work_start, on_duty, off_duty , ot_start, ot_end, work_end, punch_start,punch_end )

select a.emp_no,
a.t_date ,
@ls_shift as shift_type,
cast(a.t_date + ' ' + @wk_st as datetime) as work_start,
case when @ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @wk_ed as datetime))
else cast(a.t_date + ' ' + @wk_ed as datetime) end work_end,
cast(a.t_date + ' ' + @st_punch as datetime) as punch_start,
cast(a.t_date + ' ' + @end_punch as datetime) as punch_end,
case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @st_ot as datetime))
else cast(a.t_date + ' ' + @st_ot as datetime) end st_ot,
case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @end_ot as datetime))
else cast(a.t_date + ' ' + @end_ot as datetime) end end_ot,
pucc_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between cast(a.t_date + ' ' + @st_punch as datetime) and cast(a.t_date + ' ' + @end_punch as datetime) ),
ot_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between (case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @st_ot as datetime))
else cast(a.t_date + ' ' + @st_ot as datetime) end) and case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @end_ot as datetime))
else cast(a.t_date + ' ' + @end_ot as datetime) end )
from
(select distinct emp_no, T_date from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate)) a




fetch next from cur_emp into @emp_id, @ls_shift, @wk_st,
@wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
@end_ot, @ot_ov_night
end

close cur_emp
deallocate cur_emp

select * from #att_detail
drop table #att_detail
End
go
EXEC SP_GetDetail '2004-01-01','2005-01-01'

select * from record order by emp_no, t_date, t_time

DROP TABLE RECORD, emp_mst, shift_mst
drop PROCEDURE SP_GetDetail

[mtu207]

看的晕～

[qianduo]

对不起.我想把这代码带到公司,但没有带u盘,就发到网上来了

[lmby]

Retrieve数据后，点击COPY按钮。则允许用户保存datawindow中当前存在的这条记录。
我现在的想法也就是这个COPY相当于新增一条记录，然后把以前retrieve的数据COPY给新增的这条数据。当用户再点击保存按钮后，数据再保存在数据库中.
请问大家有什么更好的解决方案没有？

==================================================

你是freedom的?否则多条记录的话,怎么知道要新增哪条记录?还有你copy给新增记录是意味着除了主键,其他字段都和前头保存的记录一样咯?
我猜测你的目的是一条条处理,保存的同时新增一条,减少用户的输入量是吧?
那么如果每次新增记录的初值如果都一样,可以设置列的initial.
如果每次新增记录的初值是和上次保存的一样,那么在save后将上一条记录rowscopy下来,然后把主键列setnull.

没有带u盘发邮件呀,有利于保密哦,呵呵,难道油箱也没有么。

[wscft]

对不起.我想把这代码带到公司,但没有带u盘,就发到网上来了

楼主行事方法特别 特别啊! 呵呵

[da21]

RowsCopy

[lzheng2001]

楼主贴一大堆SQL上来干嘛?

[wscft]

楼主上面写的这个存储过程与你提出的问题有什么联系呢,
你过程中也没有说明语句, 不联系实际问题,谁知道是干什么的呢 计算出来几个日期
详细的说说看

[fibbery]

凑个热闹!:)

[一手软一手硬]

很简单，选择一条记录，点COPY新增一条记录，把以前选择的记录数据COPY给新增的这条数据。当用户点击保存按钮后，数据保存在数据库中。就是这么处理。