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
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,
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
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
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
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