我这个存储过程调用时候:数据插入失败:
有哪位高手给我分析一下
我的目的是 把有重复记录的Temp表过滤的clock表中;过滤条件是如果clock表中行有和temp表中AssessID,Time这两个字段同时相等就不插入 否则就插入到新表ALTER procedure [dbo].[proc_Addtab1]
(@ID int=0,
@AssessID varchar(20)=null,
@ClockTime varchar(20)=null,
@Time varchar(20)=null,
@Late varchar(20)=null)
as declare @count int
declare cur_test cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
select * from Temp open cur_test fetch cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
while(@@fetch_status=0)
begin
select @count = count(*) from Temp where AssessID = @AssessID and [Time]=@Time
if @count = 0
begin
insert into Clock
values(@ID,@AssessID,@ClockTime,@Time,@Late)
end fetch next from cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
end close cur_test
deallocate cur_test
go
有哪位高手给我分析一下
我的目的是 把有重复记录的Temp表过滤的clock表中;过滤条件是如果clock表中行有和temp表中AssessID,Time这两个字段同时相等就不插入 否则就插入到新表ALTER procedure [dbo].[proc_Addtab1]
(@ID int=0,
@AssessID varchar(20)=null,
@ClockTime varchar(20)=null,
@Time varchar(20)=null,
@Late varchar(20)=null)
as declare @count int
declare cur_test cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
select * from Temp open cur_test fetch cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
while(@@fetch_status=0)
begin
select @count = count(*) from Temp where AssessID = @AssessID and [Time]=@Time
if @count = 0
begin
insert into Clock
values(@ID,@AssessID,@ClockTime,@Time,@Late)
end fetch next from cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
end close cur_test
deallocate cur_test
go
解决方案 »
- 截String问题
- 一个触发器问题!
- sql2005 大哥大姐帮帮忙
- 求助一道 SQL难题^^^^^^^^^^^^^^^^^^^
- 各位大哥.快快帮忙啊.我UPDATA时忘了加WHERE 结果两万多条记录里的同一字段全被我更新了.
- 求一条行列转换的SQL,大家帮我写下。
- 为什么表关联不了啊?????郁闷啊,在线等解决啊
- 有很多单位,用不同的数据库,领导让我写一个有关统一数据库,制定一个数据库标准的文章。
- 一个插入或删除纪录得触发器怎么写?
- 补充字符串问题
- 超级棘手难题 数据库同步与设计
- 在连接到 SQL Server 2005 时,在默认的设置下SQL Server不允许进行远程连接可能会导致此失败。error:26
ALTER procedure [dbo].[proc_Addtab1]
(@ID int=0,
@AssessID varchar(20)=null,
@ClockTime varchar(20)=null,
@Time varchar(20)=null,
@Late varchar(20)=null)
as declare @count int
declare cur_test cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
select * from Temp open cur_test fetch cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
while(@@fetch_status=0)
begin
--select @count = count(*) from Temp where AssessID = @AssessID and [Time]=@Time 这个地方应该是从clock表中统计记录数,改成下面这句
select @count = count(*) from clock where AssessID = @AssessID and [Time]=@Time if @count = 0
begin
insert into Clock
values(@ID,@AssessID,@ClockTime,@Time,@Late)
end fetch next from cur_test into @ID,@AssessID,@ClockTime,@Time,@Late
end close cur_test
deallocate cur_test
go
select @count = count(*) from Temp where AssessID = @AssessID and [Time]=@Time
改为
select @count = count(*) from Clock where AssessID = @AssessID and [Time]=@Time
是否字段个数不对。。或类型不一。。主键是否重复。。
建议写Insert语 句为: insert into Clock (字段列表)
values(@ID,@AssessID,@ClockTime,@Time,@Late)