I’ve recently needed to have a multilingual menu where the id for each menu item is the same across all languages. This is a bit of a tall order using the workarea as only the root menu item will share the id.
So I’ve knocked out a quick’n’dirty TSQL script to clone a menu across several languages whilst maintaining the relevant ids.
1: BEGIN TRANSACTION
2:
3: CREATE TABLE #NewMenu (
4: [mnu_id] [bigint] NOT NULL,
5: [mnu_name] [nvarchar](255) NULL,
6: [mnu_description] [nvarchar](255) NULL,
7: [folder_id] [bigint] NOT NULL,
8: [recursive] [int] NOT NULL,
9: [user_id] [bigint] NULL,
10: [date_created] [datetime] NOT NULL,
11: [last_edit_date] [datetime] NULL,
12: [last_edit_lname] [nvarchar](50) NOT NULL,
13: [last_edit_fname] [nvarchar](50) NOT NULL,
14: [mnu_type] [int] NOT NULL,
15: [mnu_link] [nvarchar](255) NULL,
16: [template_link] [nvarchar](255) NULL,
17: [parent_id] [bigint] NULL,
18: [ancestor_id] [bigint] NULL,
19: [content_language] [int] NOT NULL,
20: [mnu_image] [nvarchar](512) NULL,
21: [mnu_image_override] [int] NULL,
22: [mnu_to_folders] [nvarchar](512) NULL,
23: [mnu_to_templates] [nvarchar](255) NULL,
24: [dynamic_replication_method] [int] NULL)
25: GO
26:
27: WITH MenuCTE AS (
28: SELECT *
29: FROM [dbo].[menu_tbl]
30: WHERE [mnu_id]=60
31: AND [content_language]=2057
32: UNION ALL
33: SELECT [dbo].[menu_tbl].*
34: FROM [dbo].[menu_tbl]
35: INNER JOIN MenuCTE ON MenuCTE.mnu_id=[dbo].[menu_tbl].[ancestor_id]
36: AND MenuCTE.content_language=[dbo].[menu_tbl].content_language
37: AND MenuCTE.[ancestor_id]!=[dbo].[menu_tbl].mnu_id
38: )
39:
40: INSERT INTO #NewMenu([mnu_id]
41: ,[mnu_name]
42: ,[mnu_description]
43: ,[folder_id]
44: ,[recursive]
45: ,[user_id]
46: ,[date_created]
47: ,[last_edit_date]
48: ,[last_edit_lname]
49: ,[last_edit_fname]
50: ,[mnu_type]
51: ,[mnu_link]
52: ,[template_link]
53: ,[parent_id]
54: ,[ancestor_id]
55: ,[content_language]
56: ,[mnu_image]
57: ,[mnu_image_override]
58: ,[mnu_to_folders]
59: ,[mnu_to_templates]
60: ,[dynamic_replication_method] )
61: SELECT [mnu_id]
62: ,UPPER([L].[browser_code]) + ' ' + [mnu_name]
63: ,[mnu_description]
64: ,[folder_id]
65: ,[recursive]
66: ,[user_id]
67: ,[date_created]
68: ,[last_edit_date]
69: ,[last_edit_lname]
70: ,[last_edit_fname]
71: ,[mnu_type]
72: ,[mnu_link]
73: ,[template_link]
74: ,[parent_id]
75: ,[ancestor_id]
76: ,[L].language_id
77: ,[mnu_image]
78: ,[mnu_image_override]
79: ,[mnu_to_folders]
80: ,[mnu_to_templates]
81: ,[dynamic_replication_method]
82: FROM MenuCTE
83: INNER JOIN language_type [L] ON [L].language_id IN _
(1033,1036,1034,2052,1031,1040,1046,1049)
84:
85:
86: SELECT * FROM #NewMenu [NM]
87: INNER JOIN [menu_tbl] [M] ON [NM].mnu_id=[M].mnu_id _
AND [NM].content_language = [M].content_language
88:
89:
164:
165:
166: SELECT * FROM #NewMenu
167:
168: SELECT [MI].[mnu_id]
169: ,[item_id]
170: ,[item_type]
171: ,[item_title]
172: ,[item_link]
173: ,[item_target]
174: ,[order_loc]
175: ,[item_description]
176: ,[link_type]
177: ,[id]
178: ,[NM].[content_language]
179: ,[item_image]
180: ,[item_image_override]
181: FROM [dbo].[menu_to_item_tbl] [MI]
182: INNER JOIN #NewMenu [NM] ON [MI].mnu_id=[NM].mnu_id
183:
184: DROP TABLE #NewMenu
185:
186: ROLLBACK TRANSACTION
187:
You’ll notice that this query uses a CTE (needs SQL 2005 and above) to recursively retrieve all of the menu entries. It’s also wrapped in a transaction (which will ROLLBACK until you modify the script, and I’ve commented out the section that inserts records into your table – you’ll need to uncomment it to make it work.
I’ve added inline comments to highlight what particular magic numbers mean and you’ll need to substitute this for your own. Cloned menu items are prefixed with the languages two-letter browser code.
Obviously, back up your database first and remember you’re running the script at your own risk!
I'm a lead developer for Freestyle Interactive Ltd where we create many wonderful websites built on Microsofts ASP.Net and Ektron CMS.
I've been developing .Net applications (both Windows and Web) since 2002.
Submit an article or tip
