none
unexplainable duplication of records in a tables

    Question

  • Hi,

    For some unexplainable reason my table of orders duplicated each of its records.

    I do not host the database and I do not have admin privileges to the database. I just build queries to obtain reports.

    I connect to/access a remote MS SQL server through:

    a)  MS Access 2013 ODBC connection and

    b) via SQL Server 2014 Management Studio.

    The table that got duplicated populates a ColdFusion built application that runs sales transactions and others.

    The table in question does not allow duplicates yet a few days ago I noticed that each of my 4200 records had been duplicated.

    Unfortunately the table DOES NOT have an automated ID column (so that I could sort the records by that column), but the Order_ID is generated via code i.e. Order-1, Order-2,....Order-100,...Order-201. So I have no way to tell which records were added when. I do have a date-stamp column but the duplicated records added have the same date stamp.

    Naively yesterday I appended 4200 records (containing no duplicate info) thinking they would append at 'the end' of the table that contained 8400 duplicated record. I thought that via MS Access I could manually highlight the first 8400 duplicated records and delete them manually leaving the 4200 newly added ones (that do not contain duplicates). Turns out the 4200 newly added records were added 'not at the end'  but everywhere.  Now my tbl_orders has 12 600 records with 3 copies of each record!

    There are two tables (tbl_orders tbl_skus and tbl_payments) that point to the order_ID column of tbl_orders.

    Two questions please:

    1) any idea of how these records could have been duplicated? (No body except me queries the table)

    2) What would be the best way to remove all my duplicates in tbl_orders leaving only one copy of each record?

    Your help would be greatly appreciated.

    Claudia


    Tuesday, June 17, 2014 7:02 PM

All replies

  • What does that mean ?

    "The table in question does not allow duplicates yet a few days ago I noticed that each of my 4200 records had been duplicated."

    DO you have any constraint enforcements in here, or do you mean that there should not be any duplicates?

    What does that mean ?

    "Turns out the 4200 newly added records were added 'not at the end'  but everywhere"

    TO the questions:

    1.) Querying / Selecting data does not do any persistence to the data, maybe your query is not right which will duplicate the data. Checking your query to see if any false joins occur might eveal the problem.

    2.) An easy way would be to order them by specific and individual attributes and then delete them through e.g. a CTE. If that is a production database, make sure you test that before doing it. As you don´t have any admin rights as pointed out above, make sure you contact the people that can assist you.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Tuesday, June 17, 2014 7:32 PM
  • Thanks a lot for your response Jen. Sorry for not being clear.

    Here are my answers to your questions:

    What does this mean: "The table in question does not allow duplicates yet a few days ago I noticed that each of my 4200 records had been duplicated."

    A: I only guess/suspect that the table does not allow duplicates because when I try to manually delete a record there (via MS Access ODBC connection to the table) I get the message: "Unique index contain duplicate values'  See screen shot below.

    DO you have any constraint enforcements in here, or do you mean that there should not be any duplicates?

    A: I have no access to see if there are any built in constraints to renforce unique valalues in the ORderID. I just know from years of operating this Database/building queries that the OrderID is unique, can not be repeated.  The ColdFusion code that generates/builds the OrderID does not allow duplicates.

    What does that mean ?
    "Turns out the 4200 newly added records were added 'not at the end'  but everywhere"
    A: I thought that the 4200 records I added would be listed at the end of tbl_orders but when I query or just open tbl_orders (via MS Access ODBC connection, without any sorting specified, the 4200 records that I just appended do not show/are not listed at the bottom/end of tbl_orders, they are listed as if they had been inserted in different places not all together as a block at the end of the table.

    Below my screen shots of SQL Server Mngment Studio and MS Access via ODBC connection

    Tkx

    Claudia

    p.s. Below my screen shots:

    Sorry my screen shots below were not allowed to be uploaded until I confirmed my identity. Any way I can send you my 2 screenshots?

    Tuesday, June 17, 2014 9:15 PM
  • Here are my screenshots Jen.

    https://www.dropbox.com/sh/jqa128je336usjc/AADI-wPP7dZm2_JeSyWoNonAa

    Thanks.

    Claudia

    Tuesday, June 17, 2014 9:26 PM