费了好几个小时才 搞定呀,从中可以看到很多技术.
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Neil>-- Create date: <Create Date,,20070514>-- Description: <Description,,MOVE DATA TO from a table to another table according to datetime>-- =============================================--test beginexecute sp_Move_Data '2007-05-13', '2007-05-14', 'table1', 'table2'print @@rowcountdeclare @exeCount intdeclare @temp intset @exeCount = 3set @temp = 0begin transelect top (@exeCount) * from tableName --这里是2005的新特性:top中可以加入变量,但是要加"()"的.--@temp = @@rowcountif @@rowcount = @exeCount set @temp = @exeCountcommit tranif @temp = @exeCount print @temp--test endALTER PROCEDURE [dbo].[sp_Move_Data] @beginDate nvarchar(20), @endDate nvarchar(20), @originTableName nvarchar(50), --要移出数据的表 @destinationTableName nvarchar(50) --要移入数据的表AS BEGIN DECLARE @tmpRowCount int --临时存储相应行数 DECLARE @exeCount varchar(9) --每次循环处理的语句条数 SET @exeCount = '3000' BEGIN TRANSACTION 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 > 0 OR @tmpRowCount <> @@ROWCOUNT --错误OR插入删除操作相应行数不同 BEGIN TranRollBack: print 'ROLLBACK' ROLLBACK TRAN END ELSE BEGIN print @tmpRowCount COMMIT TRANSACTION END --TRANSACTION OVER print @tmpRowCount WHILE @tmpRowCount = @exeCount BEGIN BEGIN TRANSACTION 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 > 0 OR @tmpRowCount <> @@ROWCOUNT --错误OR插入删除操作相应行数不同 BEGIN TranRollBack2: ROLLBACK TRAN END ELSE COMMIT TRANSACTION --TRANSACTION OVER END END--其实也可以用事务的嵌套的,只是那个技术我还没有掌握,以后学会了再加上吧.请各位多提宝贵意见,大家多交流呀.