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

Posted on 2007-08-01
Last Modified: 2013-11-30
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.
Question by:FillSee
    LVL 27

    Accepted 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



    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now