sql server 游标的用法

将A数据库中的一些表的数据迁移到B数据库中：

-----1.将部分临时数据从系统表中清除

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   NAME = 'ps_move_history_data' ) 
    BEGIN
        DROP PROCEDURE ps_move_history_data ; 
    END
GO
CREATE PROCEDURE ps_move_history_data
    (
      @procedureDefId INT ,
      @jobScheduleId INT ,
      @procedureExecLogId INT ,
      @rowcount INT OUTPUT ,
      @succount INT OUTPUT
    )
AS 
    BEGIN
	-----初始化字段
        DECLARE @data_move_id INT
        DECLARE @from_table NVARCHAR(200)
        DECLARE @desc_table NVARCHAR(200)
        DECLARE @desc_datasource NVARCHAR(200)
        DECLARE @move_fields NVARCHAR(2000)
        DECLARE @is_delete_all INTEGER
	
        DECLARE @condition NVARCHAR(1000)
        DECLARE @sql NVARCHAR(4000)
        DECLARE @del_keyword NVARCHAR(100)
        
		-----创建游标循环从表中将数据插入到目标库中
		-----转移的记录数记录到data_move_list表中
        DECLARE temp_move_table_Cursor CURSOR
        FOR
            SELECT  data_move_list_id ,
                    from_table_name ,
                    desc_data_source ,
                    desc_table_name ,
                    move_fields ,
                    condition ,
                    is_delete_all
            FROM    data_move_list
            WHERE   active_flag = 1
			
        OPEN temp_move_table_Cursor
        FETCH NEXT FROM temp_move_table_Cursor INTO @data_move_id, @from_table,
            @desc_datasource, @desc_table, @move_fields, @condition,
            @is_delete_all
        WHILE @@FETCH_STATUS = 0 
            BEGIN 
                BEGIN TRANSACTION
                BEGIN TRY
                    IF ISNULL(@desc_datasource, '') <> '' 
                        SET @desc_table = @desc_datasource + '.dbo.'
                            + @desc_table
					
                    SET @sql = 'INSERT INTO ' + @desc_table + '('
                        + @move_fields + ')'
                    SET @sql = @sql + 'SELECT ' + @move_fields + ' FROM '
                        + @from_table + '  WITH(NOLOCK) ' + ISNULL(@condition,
                                                              '')
					
                    EXEC sp_executesql @sql
	 
                    UPDATE  dbo.data_move_list
                    SET     last_move_time = GETDATE() ,
                            move_records = @@ROWCOUNT
                    WHERE   data_move_list_id = @data_move_id
					
					--数据迁移后，删除迁移的数据
                    
                    IF ( @is_delete_all = 1 ) 
                        BEGIN
                            SET @del_keyword = 'TRUNCATE TABLE ' + @from_table
                        END
                    ELSE 
                        BEGIN
                            SET @del_keyword = ' DELETE FROM ' + @from_table
                                + ' ' + ISNULL(@condition, '')
                        END
					  
                    EXEC sp_executesql @del_keyword
		 
                END TRY
                BEGIN CATCH
                    BEGIN 
                        IF @@TRANCOUNT > 0 
                            ROLLBACK
                        INSERT  INTO crm_error
                                ( [message] ,
                                  error_source ,
                                  error_type ,
                                  error_time ,
                                  trace_info
                                )
                        VALUES  ( ERROR_MESSAGE() ,
                                  'ps_move_history_data' ,
                                  'Procedure Run Error' ,
                                  GETDATE() ,
                                  ERROR_MESSAGE()
                                )
                    END
                END CATCH
		        
                IF @@TRANCOUNT > 0 
                    COMMIT
					
                FETCH NEXT FROM temp_move_table_Cursor INTO @data_move_id,
                    @from_table, @desc_datasource, @desc_table, @move_fields,
                    @condition, @is_delete_all
            END
		
        CLOSE temp_move_table_Cursor ;
        DEALLOCATE temp_move_table_Cursor ;
        
    END