Check if row exists on SQL

Help Studio
1

Hi community!

I have a bot that will run daily, after the execution, the bot will transfer the data from excel to SQL. How can I make sure that what the robot isn’t importing data that it exists already. In other words: If the robot fails during transferring the data, what was transferred, will stay on SQL, then i will need to fix the bot, and rerun it then, How can I make the bot to import the data that wasn’t transferred only. I have an idea but don’t know how to make it happen: Check if the rows exists.

Here is how i’m importing the data to SQL:
1- Connect to SQL
2- Read Range
3- Insert to SQL
4- Disconnect from database

Thank you for any idea

2

hi @Tarek_Kariish

What you can do is,
before inserting the data into SQL; Retrieve the data from Sql
use dt3= dt2.AsEnumerable().Except(dt1.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

to find the rows which aren’t present in sql already.
Then just insert the records present in dt3.

Thanks

Happy Automation!

3

Hi @adiijaiin,

Thanks for your answer, any idea on how can retrieve the data from SQL?

4

hi @Tarek_Kariish

you would have to write a Select Statement in Execute Query Activity.

“Select columnName1, columnName2, … from Database.TableName”

5

And just to clarify.

dt3= dt2.AsEnumerable().Except(dt1.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

dt3 = is a new variable type of DataTable
dt2 = Is the datatable that we got from retrieving the data from SQL
dt1 = is the DataTable that I want to insert to SQL

Am I correct ?

6

Yes that is correct interpretation

7

@adiijaiin ,

It is still not working, the issue is: it is still inserting the data.

I’m testing your idea with SQL data table and excel data table that contains same data but it is still inserting the data into it.

Here is what I’m doing exactly:

1- Connect to Database(SQL)
2- Read Range ( Excel file that I want get the data from it ) to dtResults var
3- Run Query “Select * from table name;”
4- Assign dt_Compared = SQL_DT.AsEnumerable().Except(dtResults.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable
5- Insert to database (SQL), DataTable that i’m inserting is the dt_Copmared.

But still inserting the data even though it is same data, which means my data is duplicated on SQL. any idea?

I’m really thankful for your help

8

Hi Can you put a breakpoint and check the values in dt_compared.

and reverse the logic to

dt_Compared = dtResults.AsEnumerable().Except(SQL_DT.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

9

@adiijaiin

Still adding the same info to SQL, I mean it is duplicating the info. and during debugging, i have notice that the data table in dt_Compared, is the same one in Excel, that means it is not comparing the data tables between Excel sheet and SQL.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.