web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Delete 170K records fr...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Delete 170K records from Inventory tables

(0) ShareShare
ReportReport
Posted on by LightingSupply 25

I have approximately 500k items in inventory tables and would like to delete 170K discontinued records with no history.

I have read various post and articles on methods. I can make a macro to delete the records it seems that this would take some time to process.

Or I can delete the records through SQL SSMS, would this method remove associated data in other tables?

Basically DELETE FROM [LSC].[dbo].[IV00101] where ITEMNMBR in ( 'SKU1', 'SKU2')

Can someone recommend the best method.

As a side note my store front is and old installation of Magento 1 which I also need to delete at the same time so they don't try and sync back to each other. I have a plan for deleting the Magento records.  My GP version is 2013.

Thanks

Categories:
Microsoft Dynamics GP
I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

    Whatever you do I would try running it on a test company.

    Using SSMS run SELECT ITEMNMBR,ITEMNMBR FROM [dbo].IV00101 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    If you agree with this list then

    DELETE [dbo].IV00101 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00102 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00103 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00104 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00105 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00106 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00107 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00108 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    DELETE [dbo].IV00109 WHERE ITEMNMBR NOT IN(SELECT ITEMNMBR FROM [dbo].[IV30200])

    This should remove all the item master records.

    After all these deletions run Check Links on the inventory series to see if anything went wrong.

    There may be pother tables if you use bill of materials or third-party products.

  • Suggested answer
    Lisa at AonC.com Profile Picture
    Lisa at AonC.com 1,062 Super User 2026 Season 1 on at

    What about using SQL to update the 170K items to be discontinued?  You could then use the Inventory Year-End Close to delete discontinued items.  (That would give GP the opportunity to do all the things it wants to do when deleting an item.)

    pastedimage1676310689595v1.png

    [As always, do the update/removal in test before attempting it in production.  And have backups.  Lots of restorable backups.  ]

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 617

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 461 Super User 2026 Season 1

#3
Syed Haris Shah Profile Picture

Syed Haris Shah 298 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans