SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE AdvKvProc
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @adv_kv_id INT
      DECLARE @kv_id INT
      DECLARE @last_id INT
      DECLARE KVCursor CURSOR FOR SELECT id FROM Ephesoft.kv_extraction
      OPEN KVCursor
      FETCH NEXT FROM KVCursor INTO @kv_id

      PRINT(@kv_id)
      WHILE @@FETCH_STATUS = 0
      BEGIN
                    select @adv_kv_id = advanced_kv_extraction_id from Ephesoft.kv_extraction where id = @kv_id;
            INSERT into Ephesoft.advanced_kv_extraction (creation_date, last_modified, display_image_name, filter_x0_coor, filter_x1_coor, filter_y0_coor, filter_y1_coor, image_name, isNormalized, key_x0_coor, key_x1_coor, key_y0_coor, key_y1_coor, value_x0_coor, value_x1_coor, value_y0_coor, value_y1_coor) select creation_date, last_modified, display_image_name, filter_x0_coor, filter_x1_coor, filter_y0_coor, filter_y1_coor, image_name, isNormalized, key_x0_coor, key_x1_coor, key_y0_coor, key_y1_coor, value_x0_coor, value_x1_coor, value_y0_coor, value_y1_coor from Ephesoft.advanced_kv_extraction where id = @adv_kv_id;
                                           
            SELECT @last_id = IDENT_CURRENT('Ephesoft.advanced_kv_extraction');
                                           
            UPDATE Ephesoft.advanced_kv_extraction_detail set advanced_kv_extraction_id = @last_id where advanced_kv_extraction_id = @adv_kv_id;
                                           
            UPDATE Ephesoft.kv_extraction set advanced_kv_extraction_id = @last_id where advanced_kv_extraction_id = @adv_kv_id;
                             
            DELETE FROM Ephesoft.advanced_kv_extraction where id = @adv_kv_id;
            FETCH NEXT FROM KVCursor INTO @kv_id
                    PRINT(@kv_id)
      END

      --CLOSE THE CURSOR.
      CLOSE KVCursor
      DEALLOCATE KVCursor
      PRINT('Executed Successfully!!!')
END
GO
EXEC AdvKvProc