set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Neil> -- Create date: <Create Date,,20070514> -- Description: <Description,,MOVE DATA TO from a table to another table according to datetime> -- ============================================= --test begin execute sp_Move_Data '2007-05-13', '2007-05-14', 'table1', 'table2' print@@rowcount declare@exeCountint declare@tempint set@exeCount=3 set@temp=0 begintran selecttop (@exeCount) *from tableName --这里是2005的新特性:top中可以加入变量,但是要加"()"的. --@temp = @@rowcount if@@rowcount=@exeCount set@temp=@exeCount committran if@temp=@exeCount print@temp --test end ALTERPROCEDURE[dbo].[sp_Move_Data] @beginDatenvarchar(20), @endDatenvarchar(20), @originTableNamenvarchar(50), --要移出数据的表 @destinationTableNamenvarchar(50) --要移入数据的表 AS BEGIN DECLARE@tmpRowCountint--临时存储相应行数 DECLARE@exeCountvarchar(9) --每次循环处理的语句条数 SET@exeCount='3000' BEGINTRANSACTION EXECUTE ('INSERT INTO ['+@destinationTableName+'] SELECT TOP '+@exeCount+ ' * FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') SET@tmpRowCount=@@ROWCOUNT IF@@ERROR>0 BEGIN GOTO TranRollBack --这里直接用RollBack语句就会有error tran begin的错误,我也不知道为什么 ,书上都是用的标签,我也用吧.不过我想写过程式的人都会很讨厌GOTO吧. END print@tmpRowCount--我真的不知道存储过程用什么Debug,只有用print语句了 EXECUTE ('DELETE TOP ('+@exeCount+ ') FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') -- print @@ROWCOUNT --这里会print 3000 -- print @tmpRowCount -- print @@ROWCOUNT --但是这里会print 0 ,@@ROWCOUNT对PRINT语句也起作用. PRINT ('DELETE TOP ('+@exeCount+ ') FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') IF@@ERROR>0OR@tmpRowCount<>@@ROWCOUNT--错误OR插入删除操作相应行数不同 BEGIN TranRollBack: print'ROLLBACK' ROLLBACKTRAN END ELSE BEGIN print@tmpRowCount COMMITTRANSACTION END --TRANSACTION OVER print@tmpRowCount WHILE@tmpRowCount=@exeCount BEGIN BEGINTRANSACTION EXECUTE ('INSERT INTO ['+@destinationTableName+'] SELECT TOP '+@exeCount+ ' * FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') IF@@ERROR>0 GOTO TranRollBack2 SET@tmpRowCount=@@ROWCOUNT print ('INSERT INTO ['+@destinationTableName+'] SELECT TOP '+@exeCount+ ' * FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') EXECUTE ('DELETE TOP ('+@exeCount+ ') FROM ['+@originTableName+ '] WHERE logTime between '''+@beginDate+''' and '''+@endDate+'''') IF@@ERROR>0OR@tmpRowCount<>@@ROWCOUNT--错误OR插入删除操作相应行数不同 BEGIN TranRollBack2: ROLLBACKTRAN END ELSE COMMITTRANSACTION --TRANSACTION OVER END END --其实也可以用事务的嵌套的,只是那个技术我还没有掌握,以后学会了再加上吧.