RVY200403: How to clean up RayVentory Database from duplicate

Prerequisites:

  • Download the attached "DuplicateCleanup.7z" file and extract them.
  • Microsoft SQL Management Studio with access to the ManageSoft database
  • Microsoft Excel 2010 or newer version

Show and Delete Duplicates:

  1. Start Microsoft SQL Management Studio with administrative rights and connect to the database
  2. Open the "Backup" file and copy the SQL-Code into a new query and execute it. 
    This will create a backup of your ManageSoft database.
  3. Open the "Duplicate_Show" file and copy the SQL-Code into a new query and execute it
    result_show.PNG
  4. Right click into the result and click "Save Results As..."
  5. Choose any filename and CSV.
  6. Open the "DuplicateAnalysis_Template.xlsx"
  7. Import the CSV-File into the Excel spreadsheet.
    result_excel.PNG
  8. Edit "Recommendation" if there are inventories you want to keep or delete anyway.
  9. For every entry with "Confirm Keep/Delete inventory" you have to decide if you want to keep or delete it. Change the text to "Keep inventory" or "Delete inventory".
  10. Save and export as CSV.
  11. Go back to Microsoft SQL Management Studio.
  12. Right click on your ManageSoft database and click "Tasks" -> "Import Data..."
  13. Press "Next >"
  14. Choose "Flat File Source" as Data Source and select your exported CSV. Pay attention to choose the correct format of your CSV (Use suggested column types my Management Studio). Afterwards press "Next >"
    1.PNG
  15. Choose "SQL Server Native Client 11.0" as Destination and press "Next >"
    2.PNG
  16. Choose "[dbo].[DuplicateList]" as Destination and press "Next >"
    3.PNG
  17. Press "Finish" and "Finish" again.
  18. After the import was successful, close the Wizard.
  19. Open the "Duplicate_Delete" file and copy the SQL-Code into a new query
  20. Execute the query to delete the duplicates
  21. Open a new query and execute following SQL-Code:
    DROP TABLE [dbo].[DuplicateList]
  22. If you want to check if you still have duplicates, repeat all steps.

 

Have more questions? Submit a request

Comments

Powered by Zendesk