How to prevent adding duplicate records in Data Table

Hi guys,
Hope you all doing good. I have a one use-case

I am adding Multiple records(ExtractedDT) in SQL table “EmpolyeeInfo” on a daily run, want to prevent adding duplicate records, I have “EmployeeID” unique column. How to achieve this?

Thanks

Welcome to the Forum @Bot_Learner

TO achieve that just make the column unique. This way you prevent fetching any duplicate entry

If you have access to update schema at SQL level. you can even make the UNIQUE constraint for that column in Schema

EmployeeID int NOT NULL UNIQUE,

1 Like

Hi @Bot_Learner

You can filter out those records from ExtractedDt which are already present in the EmployeeInfo table using EmployeeID primary key.

Make an array of EmployeeID column from EmplyeeInfo data table

empIDArr = EmployeeInfoDT.AsEnumerable.Select(Function (r) r("EmployeeID").ToString).ToArray

Filter the ExtractedDT

ExtractedDT = ExtractedDT.AsEnumerable.Where(Function (row) Not empIDArr.Contains(row("EmployeeID").ToString))

Now, you can add the ExtractedDT

1 Like

Hi

Welcome to UiPath forum

In that case we need to first fetch the table from sql only then we can find which records are duplicate and we can avoid them getting into SQL table
And we can do that using UiPath.DATABASE Activities
Install it from manage packages and use EXECUTE QUERY activity and get the output as datatable named dt_sql

On how to get the table from sql

And once after getting the datatable use a Assign activity and mention this expression to get the unmatched records

Out_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of String)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of String)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Hope this would help you resolve this

Cheers @Bot_Learner

1 Like

Dear Bot Learner,

You can merge both SQl and new Records datatable and then filter out the unique records which are to be inserted into the SQL server DB.

Based on your query as EmployeeID (let’s say int datatype) is unique we will filter the records using this column,

1.Merge the records obtained from SQL and new records in a datatable
2.Use below assign activity to filter only unique records not present in SQL DB

dtRecords=dtRecords.AsEnumerable().GroupBy(Function(i) i.Field(Of Int32)(“EmployeeID”)).Where(Function(g) g.Count()=1).Select(Function(h) h.First).CopyToDataTable

Attaching a xaml and screenshot for your reference
ForumDupRecords.xaml (8.7 KB)

Hope this helps.

Thanks,
Geetishree Rao

1 Like

Thanks @rahulsharma

Thanks @kumar.varun2

1 Like

Thanks @Palaniyappan

1 Like

Thanks @geetishree.rao

Hi @kumar.varun2
Thanks for your response, Please find the attached screen shot, i am not able to understand the error.

Hi @Bot_Learner

ExtractedDT = ExtractedDT.AsEnumerable.Where(Function (row) Not empIDArr.Contains(row(“EmployeeID”).ToString)).CopyToDataTable

CopyToDataTable was missing

Thanks @kumar.varun2