none
Retaining Duplicates

    Question




  •  I have a data flowing from outside into  table A . 

    The ID column is sometimes same for two similar records . 

    The records will be as follows 

        ID     Name           Age 

        1      Sam             blank

         1     Jennifer      blank 

         1     Jennifer       blank 



      This data has to be pushed into another table B which has a primary key column . 

        
     ServiceID  ID     Name          

          123         1      Sam             

            234       1     Jennifer     

          345        1     Jennifer      



    My problem when i push data from  A to B first time it creates a service id for each record in A . 

    These service ID's cannot be removed .

    But when Table A  gets similar set of Data or with an additional member like as follows : 

       ID     Name           Age 

        1      Sam             blank

         1     Jennifer      blank 

         1     Jennifer       blank 

         1     Jennifer     blank 

    I am not able to push it into table B since the data has duplicates . I can create my own unique key in Table A but that does not work . 

    I cannot remove the duplicates since it has to be kept since due to certain reason they  represent a new record in Table B. 

    Every time the application runs i can expect duplicates records but they have to be retained to create a new record in table B. 

    Any idea how to do it ? 





    Thanks
        




    usher A


    • Edited by usher227 Friday, April 20, 2018 8:59 PM
    Friday, April 20, 2018 6:37 PM

All replies

  • SInce the ids can repeat, only way you can do it is by either adding a new unique valued column in table A or just having a column to store the ServiceID generated in tableB back to A

    Unless either of this is there, you cant identify whether a new entry is existing in B or not and with duplicates it makes it even difficult

    So best option is to have an additional column in Table A to store back the ServiceID generated in B after pushing the data. This will also help you to identify new entries easily as they will not have a value for this column and it will be NULL

    To populate the value you can make use of OUTPUT clause to capture the generated serviceId and store it back


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, April 20, 2018 6:42 PM
  • Can you please provide a query for this ? 

    Thanks


    usher A

    Friday, April 20, 2018 6:51 PM
  • Won't work since the service runs every hr and brings sometimes similar data in Table A .

    Means Table A duplicate data will multiply .  

    If i get 4 duplicate records first time i should create 4 records in Table B 

    but the next time if i get 4 same duplicate records i should not create entries in Table B . 

    Only if the number is greater than 4 then i should create the record for the difference .


    usher A

    Saturday, April 21, 2018 6:34 AM
  • Won't work since the service runs every hr and brings sometimes similar data in Table A .

    Means Table A duplicate data will multiply .  

    If i get 4 duplicate records first time i should create 4 records in Table B 

    but the next time if i get 4 same duplicate records i should not create entries in Table B

    Only if the number is greater than 4 then i should create the record for the difference .


    usher A

    So are you telling table A data will get purged every time before new data arrives?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, April 21, 2018 6:50 AM
  • Read my old blog doe the subject 

    http://dimantdatabasesolutions.blogspot.co.il/2007/02/dealing-with-duplicates.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, April 22, 2018 5:41 AM
    Moderator