• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Sql Server 2005 Visual Studio Data Transformation - Locate duplicate data and make unique

In Visual Studio with SQL Server 2005, I am extracting one field of data from one table with a small number of duplicates and inserting it into another table where the destination field is the primary key (i.e. unique - no duplicates allowed).
I am using a source and a destination OLEDB connection object in each case.

I need help with creating a query (or Data flow transformation) that detects duplicate data and adds a character to make the 2nd, 3rd or 4th instance of the duplicate data unique allowing the subsequent destination field act as a unique identifier.
1 Solution
Hello FillSee,

I suggest the followig pseudocode

Sort source by the key field
LastValue = ""

for each record
   If keyfield = LastValue
         keyfield += counter
   LastValue = keyfield


FillSeeAuthor Commented:
Thanks Dabas, a handy addition to my script library! I also tried concatenating two fileds in the source data stream ('cust name' and 'cust code') to create a unique identifier which also worked in this case.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now