Hi Suhas,
As Shashank suggested, you might need to create a logic within your workflow where you can establish a connection using 'Set Connection' action from 'Data - SQL Server' VBO, then query your items using a Select query in a 'Get Collection' action from 'Data - SQL Server' VBO. You can check the number of the rows in the returned collection. If you have count greater than 0, you can use your insert query with 'Execute' action from 'Data - SQL Server' VBO.
I can suggest an alternate approach as well which I have employed in past for a use case with too many complicated queries. You can try to create a stored procedure if you have the access to your data base server and SQL Serve Management Studio (SSMS). I will show you an example for your reference.
In my case I have a Employee database with majorly four columns: Employee ID (Auto incremented field), Employee Name, Employee Department and Employee Salary. Now what I essentially want is to only insert new records if the record is not already present in my table. Currently, this is how my table looks like:
Now, I will create a stored procedure for the table which will accept three input parameters for Employee Name, Employee Department and Employee Salary as shown below. Here the name of the stored procedure is 'INSERT_EMP_RECORD' :
In this stored procedure, I am checking first if any record with the given input parameters exists or not. If it does not exists, then I am inserting the records only.
Now, in your Blue Prism Process Studio you can create the following workflow:
Here I have taken three variables which will be mapped to the three input parameters going ahead and I am using values such that a new record will be inserted. Now, in order to execute the Stored Procedure, I will use the following query:
NOTE: Here, N character is used to denote string values which need to be provided within single quotes preceded by this character.
Upon executing the workflow you should be able to see a new record getting inserted as shown below:
You can use similar concept while creating your own stored procedure with respect to you table and columns as well. The major benefits of stored procedure you will get is in cases when you use a lot of tables and logic related to nested queries within them.
Also just to add from a maintainability aspect in any project, Stored Procedures are much easier to maintain and modify as our Blue Prism workflow need not be drastically modified tomorrow in case any change request comes our way in near future and only what needs to be updated and tested would be this stored procedure script instead.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
As Shashank suggested, you might need to create a logic within your workflow where you can establish a connection using 'Set Connection' action from 'Data - SQL Server' VBO, then query your items using a Select query in a 'Get Collection' action from 'Data - SQL Server' VBO. You can check the number of the rows in the returned collection. If you have count greater than 0, you can use your insert query with 'Execute' action from 'Data - SQL Server' VBO.
I can suggest an alternate approach as well which I have employed in past for a use case with too many complicated queries. You can try to create a stored procedure if you have the access to your data base server and SQL Serve Management Studio (SSMS). I will show you an example for your reference.
In my case I have a Employee database with majorly four columns: Employee ID (Auto incremented field), Employee Name, Employee Department and Employee Salary. Now what I essentially want is to only insert new records if the record is not already present in my table. Currently, this is how my table looks like:
Now, I will create a stored procedure for the table which will accept three input parameters for Employee Name, Employee Department and Employee Salary as shown below. Here the name of the stored procedure is 'INSERT_EMP_RECORD' :
In this stored procedure, I am checking first if any record with the given input parameters exists or not. If it does not exists, then I am inserting the records only.
Now, in your Blue Prism Process Studio you can create the following workflow:
Here I have taken three variables which will be mapped to the three input parameters going ahead and I am using values such that a new record will be inserted. Now, in order to execute the Stored Procedure, I will use the following query:
"DECLARE @return_value int EXEC @return_value = [dbo].[INSERT_EMP_RECORD] @name = N'" & [Employee Name] & "', @dept = N'" & [Employee Department] & "', @salary = " & [Employee Salary]NOTE: Here, N character is used to denote string values which need to be provided within single quotes preceded by this character.
Upon executing the workflow you should be able to see a new record getting inserted as shown below:
You can use similar concept while creating your own stored procedure with respect to you table and columns as well. The major benefits of stored procedure you will get is in cases when you use a lot of tables and logic related to nested queries within them.
Also just to add from a maintainability aspect in any project, Stored Procedures are much easier to maintain and modify as our Blue Prism workflow need not be drastically modified tomorrow in case any change request comes our way in near future and only what needs to be updated and tested would be this stored procedure script instead.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
