Following are the generic steps to clear existing records in Database table using Stored Procedure before Data Extraction
1.Open Process Runner DB file that you have created and connect to Data Source.
2.Now, go to 'SQL Management studio', select the database that you are connected via Process Runner DB and create new query. Enter the following code and press execute, replace ‘DBName’ with Database name from the below code.
USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TruncateTable]
-- Add the parameters for the stored procedure here
@TableName varchar(50)
AS
BEGIN
Declare @sql as varchar(200)
set @sql = 'TRUNCATE TABLE ' + @TableName
exec(@sql)
END
GO
3.It will create a stored procedure under the database » <Your Database Name> » Programmability.
Note: In the below image, the Database name is 'PurchaseReq'
4.Go back to Process Runner DB » Start/End Script Setting » Pre/Post Run Process settings » Check-mark the 'Pre-Run Process Settings' » Select 'Stored Procedure (Pre-Run Stored Procedure)'.
5.Go to 'Pre-Run Stored Procedure' tab and select 'TruncateTable'.
6.In this same window, provide the table name in 'Parameter Value' column. Table name should be exactly same which is mapped in the Process file.
7.Click OK and save the Process file.
Next: