How to INSERT new datatable into a SQL table or UPDATE data if it already exists?

Help Studio
, ,
1

Hello. I recently started using databases in UiPath. My process should read an Excel file and insert the data into an existing MS SQL Server table. The problem is that if the record already exists I should perform an UPDATE otherwise an INSERT.
Also, I would like to insert batch data using the datatable generated from the Excel file and not loop for each row of the datatable.
I don’t know if I should use a Stored Procedure or use MERGE. Please help me because I am not able…
Thanks a lot to anyone who can help me find the solution.

2

Hi

Welcome to uipath forum

Hope the below steps would help you resolve this

  1. Go to design tab → manage packages-> all package → search for UiPath.Database.Activity and install it

  2. Then use a Execute query activity and use a sql statement to get the already existing data as a datatable variable named dt_input

  3. I assume you have a variable dt having the data to be uploaded to that sql database

  4. Now use a assign activity like this to get the in common records between these two datatables

dt_UnCommonRows = dt_input.AsEnumerable().Except(dt.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

  1. Now use a EXECUTE NON QUERY ACTIVITY and pass this dt_unCommonRows as input with Insert method

This will insert only the new records if any and won’t insert the already existing records

Cheers @Nebur

3

What is your criteria for “if the record already exists?”

4

I mean if the unique ID to insert is already present inside the SQL table, I have to UPDATE otherwise INSERT a new record.

5

Thanks a lot.
But I would have to run the process once an hour, so querying the database to retrieve existing records might not cause performance issues as the data on the database table grows?
Instead to find and update existing records what should I do?

Thank you so much if you can post an example.

6

So do a select query to see if it already exists. If/Then - if it exists, run update query, otherwise run insert.

7

There is no other way. You have to query the db to see if the record is already there. How else do you think you’d find it?

1 Like
8

So @postwick I’m forced to loop for each row of the datatable to see if it already exists in the database?
I think I can create a Stored Procedure or a MERGE query that was able to execute the INSERT and UPDATE in a single query.

MERGE [devLaserViso].[dbo].[Machine] t
USING (SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; Database=C:\Project\ExcelName.xlsx’, [SheetName$])) AS s
ON (s.IdName = t.IdName)
WHEN MATCHED THEN
UPDATE SET t.MachineName = s.MachineName, t.ProgramName =s.ProgramName
WHEN NOT MATCHED BY TARGET THEN INSERT (IdName,MachineName,ProgramName) VALUES (s.IdName,s.MachineName, s.ProgramName);

I think I could create a Stored Procedure or a MERGE query that was able to execute the INSERT and UPDATE in a single query.
I thought of a MERGE that was able to read an Excel file from a path… but I don’t know if it can be done. It does not work. Help me please! @postwick @Palaniyappan