Hi Team,
I am trying to execute a insert query (hardcoded) by including it in a Stored procedure using below syntax:
CREATE PROCEDURE <sp_name>
AS
BEGIN
<INS_Query>
END
But it gives a validation error : “Query cannot contain keywords create, update, delete, alter, drop.”
Please guide how to write a SP for Update/Insert queries.
Hi,
Only select operation is supported as part of Stored Procedure
Regards,
Amit
Hi Amit,
Thanks for reply, but in another post reply from your team was to include update query in stored procedure and use ‘Execute DB Query’ with Database Operation=Stored Procedure.
So isn’t there a way we can execute queries other that Select to automate DB Queries ?
Thanks.
Hi Vigantha,
For security purpose only Select and Insert query support has been provided
Regards,
Amit
Hi amit,
In 18.0 Version the update is also possible by using Execute Database Query control.
Thanks
Satya
Hi Satyavani,
I have 18.0 version, but if I include query with Update statement, it shows me warning that certain keywords like ‘create,update,delete,alter,drop’ cannot be used in Execute DataBase Query activity.
Is there any other way we can execute “Update” query ?
Hi Vignatha,
In select Database Operation we need to use StoredProcedure option,Query is SP name as argument, and the parameters in the sp also need to pass as the arguments what ever required.
Thanks
Satya
Hi Satyavani,
Do I need to select the checkbox or not ? and in argument, is there any specific argument type to be selected to mention it as SP?
Hi,
Steps that you can follow:
Step 1:
You can use an Assign activity before the Execute DB Query and assign the query there, instead of passing it as a default value in the Arguments section.
Step 2:
Add a Execute DB Query activity and set the Database Operation=Stored Procedure, Query=your query argument, which you created in Step 1. You need not select the checkbox against the query.
Step 3:
Map the stored procedure parameters by clicking on the Settings button of Execute DB Query --Input Mapping (for input params) and Output Mapping (for Output params)
Hi Vignatha,
Declare the argument sp1 has your stored procedure name and remaining parameters which are delared in db stored procedure.
By clicking on the above settings button for Execute Database Query component. We can pass the arguments according to the sp. Then update query also possible with the stored procedure option. Please try in this way. Its work definetly.
Thanks
Satya
Hi Anshu & Satyavani,
I have a Update query with syntax ‘update table_name set col1= " " and col2= " "’.
I am unable to execute this query in above specified two ways. It gives me error, query cannot contain ‘update’ keyword. Please suggest a way to execute this query and it is not a Stored Procedure.
Hi Vignatha,
Which is the DB you are working with? And, are you able to establish the connection with the DB? Lastly, is select query working?
Thanks
DB : SQL server. Yes, I am able to establish the connection and execute Select & Insert queries.
Hi Vignatha,
Since UPDATE essentially is deletion + insertion, we lose the data prior to UPDATE once it is over-written. Hence, we have not allowed “UPDATE” statements.
However, this can be handled via a work-around as detailed below:
• Consider a scenario, where we have a table “UserDetails” with 2 columns – Name and Email.
• Suppose there is a need to update Email using Name as the unique identifier.
• In order to handle this, we need to create an additional table, say “UserDetails_RPA”, with same fields and do insertions in these new table.
• In order to update the Email in original table, we can create an INSERT based trigger on the new table.
Thanks
Anshu
Hi Anshu,
Should we create new table in our database ? And after we insert in new table, how will that reflect in the original table. Please advice.
Hi Anshu,
Please advise.
Yes. Create a new table with the same columns. And, then create an insert trigger on this new table, which will update your column in the original table. This trigger will execute automatically whenever you insert a row into it.
