Remove Records from DB Table Using Stored Procedure Before Data Extraction

Perform the following 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 DataSource.

   

2. Go to SQL Management studio, right-click on the database that you are connected via Process Runner DB and select New Query.

   

3. 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
   

   

4. It creates a stored procedure under the database » <Your Database Name> » Programmability.

   In the below image, the Database name is PurchaseReq.

   

5. Go back to Process Runner DB and select Edit tab > Script and Delays Options > Pre Script.
6. In the Pre-Run/Post-Run Process Settings window, select the Pre-Run Process checkbox.
7. Select Stored Procedure from drop-down menu.
8. Select TruncateTable from drop-down list of Stored Procedures and select OK.

   

9. In this same window, provide the table name in Parameter Value column. The table name should be exactly same which is mapped in the Process file.

   

10. Select OK and save the Process file.