How to export and import Menus from one Database to Another

:warning: I was using SQL Express 2014 and Microsoft SQL Server Management Studio 2014 for this.

I will show you how to use Microsoft SQL Server Management Studio to Export your Menu and Products into a SQL Script so you can use Database Tools to import your entire Menu if you ever need to rebuild your database for some reason or you want to use a non production computer to build a menu and then import it to your production system.

For whatever reason this may be useful to you here is a simple method:

:warning: This will not show you how to migrate recipes or inventory items


Right click your database and select:

Tasks > Generate Scripts

Press Next from the Introduction:

Choose Select specific database objects and select the following objects then press Next:

Be sure Save scripts to a specific location is checked and then click Advanced:
Leave everything default except two settings:

Script Drop and Create > Script Drop and Create
Types of data to script > Data only

Press OK:

Edit the new script file you just created and delete the following lines ***(DatabaseName will be your database)***:

USE [DatabaseName]
GO

Save the file as and put it inside your MyDocuments > SambaPOS4 > Database Tasks:

[CBL]Copy Menu Items

You can now import your menu using Database Tools anytime you need too.

6 Likes

how about for sql 2012…similar steps?

Yes, similar, if not identical. Try it… you’ll find out quickly if it is the same or not.

How to import menus with out database tools. can i use Sql Management :wink:?

Create a backup, restore it.

I am following this tutorial but I ran into an IDENTITY_INSERT set to off error
Where do I set it on?

You probably do not want to turn that on. If you do, you could have duplicate [Id]s for Menu Items in the Database which is a very bad thing.


Backup your DB.

Open the exported SQL file (using SSMS or a text editor or whatever).

Find the first chunk of INSERT lines (which will be after CREATE TABLE), and put this in there:

-- SET IDENTITY_INSERT to ON.  
SET IDENTITY_INSERT dbo.TABLENAME ON;  
GO  

Obviously, change the TABLENAME to be the applicable Table that was just created and the INSERT statements are going to act on.

Find the last INSERT statement for the Table, and add this:

-- SET IDENTITY_INSERT to OFF.  
SET IDENTITY_INSERT dbo.TABLENAME OFF;  
GO  

Scroll down to the next batch of INSERT statements and add the same code before and after the INSERTs, while making sure you change the TABLENAME to be the same as the Table being used in the INSERT statements.

Repeat as necessary for all Tables.

I check the content of the sql file and it seems to have all those setting on and off done correctly already.

Here is part of the file content:

DELETE FROM [dbo].[MenuItemPriceDefinitions]
GO
DELETE FROM [dbo].[MenuAssignments]
GO
DELETE FROM [dbo].[MenuItemPrices]
GO
DELETE FROM [dbo].[MenuItemPortions]
GO
DELETE FROM [dbo].[MenuItems]
GO
DELETE FROM [dbo].[ScreenMenuItems]
GO
DELETE FROM [dbo].[ScreenMenuCategories]
GO
DELETE FROM [dbo].[ScreenMenus]
GO
SET IDENTITY_INSERT [dbo].[ScreenMenus] ON

INSERT [dbo].[ScreenMenus] ([Id], [CategoryColumnCount], [CategoryColumnWidthRate], [SelectedCategoryFormat], [SelectedSubCategoryFormat], [Name]) VALUES (1, 1, 25, NULL, NULL, N’Menu’)
SET IDENTITY_INSERT [dbo].[ScreenMenus] OFF
SET IDENTITY_INSERT [dbo].[ScreenMenuCategories] ON

INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (6, N’Dessert 甜點’, NULL, 0, 120, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (7, N’Beverage 飲品’, NULL, 0, 110, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (8, N’Soup Noodle 汤面’, NULL, 0, 30, 1, 0, 0, 0, N’Green’, 24, 1, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, N’Downloads\beef_noodle_soup1.jpg’, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (9, N’Congee 粥’, NULL, 0, 60, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (10, N’Fried Noodle 炒面’, NULL, 0, 40, 1, 0, 0, 0, N’Green’, 24, 1, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (11, N’Ice Cream 雪糕’, NULL, 0, 100, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (12, N’Pan Mix Noodle 干捞面’, NULL, 0, 50, 1, 0, 0, 0, N’Green’, 24, 1, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (13, N’Rice 饭’, NULL, 0, 20, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (14, N’Appetizer 小吃’, NULL, 0, 70, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (15, N’Rainbow Ice 彩虹冰’, NULL, 0, 80, 1, 0, 0, 0, N’Green’, 24, 1, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (16, N’Bubble Tea 泡沫茶’, NULL, 0, 90, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
INSERT [dbo].[ScreenMenuCategories] ([Id], [Name], [Header], [Appearance], [SortOrder], [ScreenMenuId], [MostUsedItemsCategory], [ColumnCount], [MenuItemButtonHeight], [MenuItemButtonColor], [MenuItemFontSize], [WrapText], [PageCount], [SortAlphabetically], [MainButtonHeight], [MainButtonColor], [MainFontSize], [SubButtonHeight], [SubButtonRows], [SubButtonColorDef], [NumeratorType], [NumeratorValues], [AlphaButtonValues], [ImagePath], [NumberPadPercent], [MaxItems]) VALUES (17, N’Special 特類’, NULL, 0, 10, 1, 0, 0, 0, N’Green’, 24, 0, 1, 0, 0, N’Orange’, 26, 75, 1, N’’, 2, NULL, NULL, NULL, 45, 0)
SET IDENTITY_INSERT [dbo].[ScreenMenuCategories] OFF
SET IDENTITY_INSERT [dbo].[ScreenMenuItems] ON

Try running the script through SSMS instead of using the SambaPOS utility.

1 Like

Yes! Running the script without modification through SSMS instead of using the SambaPOS ut works.

Now, until the db tools in SambaPOS is enhanced/fixed to take care of this scenario, I will need to change this into a SQL script that user can just run it from SambaPOS to obtain update of, say, menus. It would not be reasonable to ask the restaurant operator to learn/use the MS SQL Management Studio.

Thanks.

The reason this SQL method is even here is because DB Tools does not have this functionality.

That probably will never happen, at least not in V5, or it would have been done already.

There are other ways to “automate” this though, such as the client clicks a button which fires the Start Process action which executes a BAT file that invokes sqlcmd.exe to run the script.

1 Like

You can also use SQL in configuration tasks. And we can even use the execute Configuration Task action. You could even make it prompt asking for the item and the menu you want to add the item too. Allowing individual items to be added. Same thing can be done to remove items or menus etc.

So yes it has already been implemented just nobody uses it because its got a learning curve to learn how to use the feature. Personally I didnt find it that hard though. It just took the initial commitment to sit down and learn it. I learned how to write config tasks in less than 3 hours.

3 Likes