CREATE Procedure SP_T_TestData_AutoRefresh
@flag int=0 output
As Begin Tran
Declare @ID char(10)
Declare @TDate Datetime
Declare @error int --错误信息 --从生产Northwind表中更新 --声明游标
Declare CURAR_DB CURSOR FOR
Select ID,Cast(TDate as Datetime) from Northwind.dbo.T_Date where Bautorefresh is NULL
--打开游标
Open CURAR_DB
FETCH Next FROM CURAR_DB Into @ID,@TDate --调试时这个会报错,我想报错的记录删除,该如何做
--
WHILE(@@FETCH_STATUS = 0)
Begin
IF (Select COUNT(ID) From T_TestData where ID=@ID)=0 --判断这条记录是否存在,存在则更新,否则修改
Begin
Insert Into T_TestData(ID,TDate) Values(@ID,@TDate)
Set @flag = @flag+@@error
End
Else
Begin
UPdate T_TestData
Set TDate=@TDate where ID=@ID
Select @flag= @flag+@@error
End
--更新标志
UPdate NorthWind.dbo.T_Date Set Bautorefresh=1 where Bautorefresh is NULL and ID=@ID
--next curor
FETCH Next FROM CURAR_DB Into @ID,@TDate
End --Roollback
IF(@flag <>0)
Begin
--写入别一个表
IF (Select COUNT(ID) From T_TestDataTemp where ID=@ID)=0 --判断这条记录是否存在,存在则更新,否则修改
Begin
Insert Into T_TestDataTemp(ID,TDate) Values(@ID,@TDate)
End
Else
Begin
UPdate T_TestDataTemp
Set TDate=@TDate where ID=@ID
End
--删除出错表
Delete From Northwind.dbo.T_Date where ID=@ID
--roolbackup
ROLLBACK TRANSACTION
End
Commit Tran Close CURAR_DB
Deallocate CURAR_DB --
GO
所以只把把出错的日期现抛出来,手工改过之后,再导进去,不然,其他的记录就不能正常更新
-----------------------------------------------------------------------这样的话就先找出所有不合法的数据:
Select ID,TDate
from Northwind.dbo.T_Date
where Bautorefresh is NULL
AND TDate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'更改之后再导入.