Update table query in SQL databse

Hello,
I needed to update the data in the table I’ve already entered in SQL databse. The columns name in the table called Inv_DT are:

• invoice_key (primary key)
• company_code (primary key)
• amount
• status

If primary keys match (allreday stored in SQL vs. new data), the robot will update the columns amount and status.

Thank you very much,
Marek

Are you looking for a SQL statement to update the Inv_DT?

If so, it wold be good to look at SQL_Update

Once you have your base statement, you can then add your variables for invoice_key and company_code as this link Using Parameters in Non-Query

Hope that helps

Hello Timk,

yes, I need to update the table, but I have a problem with technical parts - how to do it all at Uipath. Use query / non query activity? And how to fill panel properties also. I was trying to set it up for 3 hours but with no results.

Thank you for any help.
Marek

To Update / Insert / Delete you should use the Execute Non-Query

In that activity properties:
Connection Config - Open a connection using the connect activity and pass in the existing database connection from there.
Input -
-SQL = Update SQL statement. Your query will look something like below:
“UPDATE [TABLE] SET Amount = ‘55’, Status =‘Active’ WHERE invoice_key = @Invoice_Key AND company_code = @Company_Code
-Parameters =
- Name = @Invoice_Key / Value = varinvoice_key
- Name = @Company_Code / Value = varcompany_code
Output -
- Affected Records -Returns number of updated items

Hope that helps, read the link to understand the activity more :slight_smile:

Hi TimK,

I am trying to add columns in EXCEL.By connecting excel as a database.
I am using ALTER statement to add column.
ALTER TABLE [TABLE_NAME$] ADD [COLUMN_NAME] VARCHAR(100);

but this is not working.
can you help me with this.
Thanks in advance.

Hi @ravi.g6

So you want to add columns to excel file or datatable within UiPath or database table?

If its database table then your query is correct :

ALTER TABLE table_name
ADD column_name datatype;

(make sure you have the correct database privilages to amend datatables etc)

I want to add column to Excel with UIPath.
where I have connected Excel as a Database.

do you have an example of your excel / database structure and what column you want to add in excel?

If you read excel file - that will be a datatable in UiPath - you can then use add data column activity to add new column.

You can then write this back to excel and it will include new column

Yes, I know that we can use add data column activity to add new column in Excel.
but what I am automation is complex so I thought of using Excel as a database.
There are multiple other things which I am doing and are working fine like Select Query is working but to add column using Alter Query is not working.

Please refer my post - Adding column using Alter statement in Excel as a Database

Thanks :slight_smile: