How to export and import Menus from one Database to Another

Jesse · January 18, 2015, 10:53pm

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:

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.

madiha · January 19, 2015, 5:52pm

how about for sql 2012…similar steps?

QMcKay · January 19, 2015, 6:05pm

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

honey3d40 · May 10, 2015, 9:09pm

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

emre · May 10, 2015, 9:14pm

Create a backup, restore it.

WingoLED · October 4, 2017, 3:58am

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

QMcKay · October 4, 2017, 11:53am

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.

WingoLED · October 4, 2017, 6:06pm

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

QMcKay · October 4, 2017, 6:11pm

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

WingoLED · October 5, 2017, 3:22pm

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.

QMcKay · October 5, 2017, 5:47pm

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.

Jesse · October 5, 2017, 6:10pm

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.

aru1gj01 · January 17, 2022, 12:06am

Hi,
I try to follow your instruction and when I click Execute in Task, it come up with an error

I don’t know how to fix this?

Please kindly assist.

Jing

Posflow · January 17, 2022, 12:09am

What its trying to say is that you will have duplicate IDs and this will be an even bigger problem for you.
My assumption is that this tutorial was meant to work when you copy from an existing database to brand new one where there cant possibly be a duplicate

aru1gj01 · January 17, 2022, 12:15am

OH… I see.

I just try to duplicate the menu and import to different branches of the restaurant. They have 2 branch same menu but different location… I don’t know what will be the easiest way…
Jing

Posflow · January 17, 2022, 12:26am

Until Neptune is out, this will have to be a bit of manual work and cross checking.

I usually prepare spreadsheet with items and then compare/add when its missing.

aru1gj01 · January 17, 2022, 12:29am

Thank you very much…

Memo · January 17, 2022, 1:17am

These errors are because the insert statements contain the identity column value which is auto generated by the db on insert.

For each table one would have to SET IDENTITY_INSERT to ON then then OFF afterwards. The problem is, if you’re importing this into a database that is already configured when you could run into duplicate values in the identity column.

If you’d like to import into a fresh database, change “Data only” to “Schema and data” and then the generated script will completely drop (delete) the table and recreate it, then insert the data.

I can’t recall off the top of my head if there are any references to those tables elsewhere in the db, but if there are foreign keys referencing anything in the tables to be imported the script will fail.

aru1gj01 · January 17, 2022, 2:39am

Hi,
Thank . I run the script in the Microsoft SQL and it is now working fine.

Jing

Jesse · January 17, 2022, 2:27pm

Neptune will solve this for you.