Delete Products and Categories using SQL

V5 Question
1

Hi Guys,
Just wonder is there are any SQL script which can delete All the products and categories in one go ?

Regards
Dylon

2

This should clear-out everything product related.

BEGIN TRANSACTION;
BEGIN TRY
  DECLARE @ErrorMessage NVARCHAR(MAX);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  DELETE FROM dbo.ScreenMenuItems;
  DELETE FROM dbo.ScreenMenuCategories;
  DELETE FROM dbo.RecipeItems;
  DELETE FROM dbo.Recipes;
  DELETE FROM dbo.MenuItemPrices;
  DELETE FROM dbo.MenuItemPriceDefinitions;
  DELETE FROM dbo.MenuItems;

  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    BEGIN
      ROLLBACK TRANSACTION;

      SET @ErrorMessage = ERROR_MESSAGE();
      SET @ErrorSeverity = ERROR_SEVERITY();
      SET @ErrorState = ERROR_STATE();
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;
3

I have used the SQL code that you kindly provided. It does remove all and the menu entries and products. Further more, SambaPOS can still report past activity

Sometimes a product catalog reset is necessary. Hiding products may not be practical

Is this a safe way to reset products if you really need to?

I have tried another way by clearing the menus and deleting all products from the SambaPOS UI. This breaks past activity reports

On each order into the database there is a reference to the product and this references is are the reason of my concern

Capture

4

The only thing I can think of would be to have one menu item called, for example, “REMOVED” that gets set for MenuItemId in the orders table. Delete the screen menu item, menu item, then update orders table to reflect the the “REMOVED” menu item when a match to the MenuItemId is not found.

SQL to come shortly.

5

Thanks for your answer @Memo The MenuItemId refers to the ID of the product. As I understand the MenuItems table represents the products and the reference to the product ID is kept in the MenuItemId table column

6

Orders and Screen Menu Items both reference back to the Menu Item (product).

Once you remove the screen menu items, then menu items, run the DML below to update the orders table. Set the variable @MenuItemName to whatever menu item you’ve created or want to create. If it doesn’t exist, it’ll be created.

See how the reporting looks. This will also allow reprinting of tickets. IIRC, printing crashes if there’s no match for the menu item.

As always, backup the db before manipulating the SQL and preferably test in a non-PROD environment.

BEGIN TRANSACTION;
BEGIN TRY

  /*Set string value to menu item name to use for removed menu items*/
  DECLARE @MenuItemName NVARCHAR(MAX) = N'REMOVED';

  /****** DO NOT EDIT BELOW THIS LINE ******/

  DECLARE @MenuItemId INT;
  DECLARE @PortionId INT;
  DECLARE @ErrorMessage NVARCHAR(MAX);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT @MenuItemId = Id
      FROM dbo.MenuItems
      WHERE Name = @MenuItemName;

  IF (@MenuItemId IS NULL)
    BEGIN
      INSERT INTO dbo.MenuItems
      ( GroupCode,
        Barcode,
        Tag,
        CustomTags,
        ItemType,
        Name)
      VALUES
      (NULL, NULL, NULL, NULL, 0, @MenuItemName);

      SET @MenuItemId = SCOPE_IDENTITY();

      INSERT INTO dbo.MenuItemPortions
      ( Name,
        MenuItemId,
        Multiplier)
      VALUES
      ('Normal', @MenuItemId, 0);

      SET @PortionId = SCOPE_IDENTITY();

      IF (@PortionId IS NULL)
        BEGIN
          SET @ErrorMessage = N'Portion could not be created.';
          SET @ErrorSeverity = 15;
          SET @ErrorState = -1;

          RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        END;

      INSERT INTO dbo.MenuItemPrices
      ( MenuItemPortionId,
        PriceTag,
        Price)
      VALUES
      (@PortionId, NULL, 0);
    END;

  IF (@MenuItemId IS NULL)
    BEGIN

      SET @ErrorMessage = N'Menu item named ' + @MenuItemId + N' was not found.';
      SET @ErrorSeverity = 15;
      SET @ErrorState = -1;

      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;

  UPDATE o
      SET o.MenuItemId = @MenuItemId
      FROM dbo.Orders              o
           LEFT JOIN dbo.MenuItems mi
                  ON mi.Id = o.MenuItemId
      WHERE mi.Id IS NULL;

  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    BEGIN
      ROLLBACK TRANSACTION;
      SET @ErrorMessage = ERROR_MESSAGE();
      SET @ErrorSeverity = ERROR_SEVERITY();
      SET @ErrorState = ERROR_STATE();
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;
2 Likes
7

All of this is good conversation however I must caution you should avoid directly manipulating sql for these type of situations it can almost certainly cause unwanted outcomes that can be hard to reverse. Even if you backup a database you may think all is well and discover later down the road it wasnt and at that point the database backup is useless because it is no longer up to date.

I am not saying anyone is wrong here especially Memo he is very thought out with his advice. I am just warning that directly manipulating SQL is not usually a good solution. This case in particular doesn’t seem to pose a risk if we update database with future versions but that is another caution about manipulating SQL like this. Sometimes we may change a database Schema or relation with future updates and you may see crashes etc that you didn’t anticipate that you normally would not see.

1 Like
8

Typically the best method for these situations is just to remove them from the menu and then rename them with XXX in front so it drops them to bottom, And make a category called XArchived Products for them.

If you ever need to revive the product just remove the XXX and set the appropriate category back.

1 Like