SQL database Query - Execute Non Query/Query

Hi,

I am trying to insert into a local database. I have the database connection ready and I want to run the following query to append the existing table in the database “fsv_test”. Can you please give some advice? I use exactly the same query in SQL Management Studio and successfully update the table but can’t make it work through UiPath database activity.

“USE fsv_test;
GO
Insert Into test_connection1
Select * From Openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0; Database=C:\Users\Test_1.xlsx’, [Sheet1$]);”

image

I read something about stored procedure but I am not sure if it is applicable to this case???

Thanks!

Lavina

Hello,

are you inserting into table here or running the Stored procedure by passing arguments?
if inserting to a table you can directly use the query
insert into TABLE_NAME values ();
but if you running stored procedure then you can call procedure directly instead of writing the whole procedure code in the Edit query section.

Thanks,
Meg

I don’t know about stored procedure and I tried to just use the query as shown above but get the error

“.Net SqlClient Data Provider : Incorrect syntax near ‘GO’.”

what activity exactly do I have to use? I just want Uipath to run the following query to append the table

“USE fsv_test;
GO
Insert Into test_connection1
Select * From Openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0; Database=C:\Users\Test_1.xlsx’, [Sheet1$]);”

Hi,

Before using the execute query/execute non query try to understand the purpose of these activities.
The ExecuteNonQuery Method returns the number of row(s) affected by either an INSERT , an UPDATE or a DELETE.
The ExecuteQuery Method returns the ResultSet objectwhen we use the SELECT statement.

now you are using the select query so use the ExecuteQuery activity, also before making using this you have to use the Connect activity for the connector parameter to be passed.

I will attach one of sample xaml with mysql connector and select query, please have a look, hope it helps.
MySQL_Test.xaml (7.8 KB)

Meg

Thanks! But I am running the below query instead of only the select statement

“USE fsv_test;
GO
Insert Into test_connection1
Select * From Openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0; Database=C:\Users\Test_1.xlsx’, [Sheet1$]);”

Can you please do an example to show exactly how to implement the above query? Thank you!

I tried to do this

But got an error
image

Insert and select should all be in one run though… It’s inserting what it selects…

The excel file that I have has >15 columns. Do I have to make variables for all columns? and use for each data row? This method I assume is too slow