Bulk Insert Database Activity- using Datatable content, getting Error: Missing expression

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:

  1. Both databases are having the same column names and same data types.

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

Any help would be appreciated.

@keerthims I think there is syntax error in your query

1 Like

Thank you so much for your response Indra.
Below is the query i used in UAT environment.

SELECT * FROM MASTERKEYSYSTEMNOTE WHERE MASTERKEYSYSTEMID IN (SELECT MASTERKEYSYSTEMID FROM MASTERKEYSYSTEM WHERE STRUCTURENO = ‘KS24792’)

The queried result “Execute Query” has been kept in Data-Table. Same Data-table has been used to insert into QA environment.

Do you see any issues with the query above?

1 Like

Thanks All,

It worked for me. The table results were empty and thats why this error was.

1 Like

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?

1 Like

Are you getting error while insert? What is the exact error?
Will it work when you manually perform insert from SQL?

1 Like

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

1 Like

Any solution?

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.

I went with option B myself

1 Like

@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’.

Any idea ?

1 Like

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.

1 Like

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

1 Like

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

Upload Selected Samples.xaml (13.2 KB)

2 Likes

thank you Dave. :slight_smile:

@Manjunath_Ramappa,

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.

StrDump-INSERT.zip (1.5 MB)

1 Like

Thank you for the detailed example @keerthims