I am trying to do database dump activity from one environment to another. Initially I am connecting to UAT environment, run the select query and save the result in Datatable. Then connecting to QA environment and using “Insert” database activity to dump data-table results (which is taken from UAT)
When using “Insert” database activity, UIpath will give error saying- “Insert : ORA-00936: missing expression”. How to resolve this error? How in general we can resolve issues found from Insert Query? Because What i am doing is fetching query results to data table and trying to insert the same data-table content in different environment.
Findings:
Both databases are having the same column names and same data types.
Since column names are more and types are unknown, i do not want to use “Execute non Query” command. Need input on how to dynamically bulk insert Values from data-table.
I’m having a similar issue. I have some field names that contiain spaces, parentheses, or number signs (#) and they seem to be causing a syntax error for me.
I am using a select all query, cloning the datatable, adding a single row of information, then trying to insert the datatable back into the original database table. However, I keep getting syntax errors and have no idea how to get around it. Any ideas?
The error is only when using the Insert database activity. If the column name contains the number sign (#) it gives a syntax error that the date is in an incorrect format. If it has the other characters listed in my previous post, it gives a syntax error for a missing operator
Everything works fine when running a non-query Insert statement. However, it’d be much more useful if I could use the Insert database activity as I could directly pass the datatable into the database, which is much more efficient then creating a non-reuseable SQL non-query and having to go through each row in the datatable to insert one at a time
I don’t think there’s a solution, it’s just the way the UiPath team built the activity.
The only workaround is to A). create your own Insert database activity that automatically encapsulates field names with brackets or B). To just use execute non query activity and build your own INSERT query.
@Dave were you able to insert a datatable using insert activity later ? or could you please provide non query insert statement to insert each row from a datatable ?
when i try to insert a datatable using insert activity, it works for simple datatable containing a very few columns and rows and not for huge files with mixed data types. i get error
RemoteException wrapping System.Data.SqlClient.SqlException: Incorrect syntax near ‘Code’.An expression of non-boolean type specified in a context where a condition is expected, near ‘U’.
Yes I was but I am gone for an extended weekend so I won’t be able to provide an example. That syntax error is usually due to special characters or spaces in column names, is every column surrounded by a square bracket?
Also, I believe I had to insert one datarow at a time, I don’t think I ever got a full datatable to pass at once to an access db.
@Dave, thank you for your reply, could you please provide the workflow or syntax to insert one datarow at a time? I am trying insert data into mssql server.
@Manjunath_Ramappa I don’t have any old workflows inserting into SQL server as our IT dept doesn’t like RPA to be doing that and forces us to use the existing controls setup for existing UI instead. However, I have attached a project that is updating an access database which is essentially the same syntax as SQL server. The key things are that column names should be surrounded by [square brackets] and that if you are using parameters they must be listed in sequential order in uipath.
Try looking into this. This is working for us… You need to extract what you need. in this use case, we are moving required data from one environment to another using Insert.
ashokkarale
anjani_priya
Dheerendra_vishwakarma
Parvathy
Aakash_Singh_Rawat
Luis_Fernando
bjorn2390
neco
pere
Shiva_Nikhil