Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Ektron: SQL Script To Clone A Menu into Multiple Languages

, 1 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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 across several lan

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 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 -- MENUID TO CLONE
  31:   AND [content_language]=2057 -- LANGUAGE OF MENU TO CLONE
  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: -- Clone menu
  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--[content_language]
  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) -- LANGUAGES TO CLONE INTO
  84:  
  85: -- Conflicting Rows
  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: /*  uncomment this block when ready to add new menus! 
  90: INSERT INTO [menu_tbl]([mnu_id]
  91:       ,[mnu_name]
  92:       ,[mnu_description]
  93:       ,[folder_id]
  94:       ,[recursive]
  95:       ,[user_id]
  96:       ,[date_created]
  97:       ,[last_edit_date]
  98:       ,[last_edit_lname]
  99:       ,[last_edit_fname]
 100:       ,[mnu_type]
 101:       ,[mnu_link]
 102:       ,[template_link]
 103:       ,[parent_id]
 104:       ,[ancestor_id]
 105:       ,[content_language]
 106:       ,[mnu_image]
 107:       ,[mnu_image_override]
 108:       ,[mnu_to_folders]
 109:       ,[mnu_to_templates]
 110:       ,[dynamic_replication_method] )
 111: SELECT [mnu_id]
 112:       ,[mnu_name]
 113:       ,[mnu_description]
 114:       ,[folder_id]
 115:       ,[recursive]
 116:       ,[user_id]
 117:       ,[date_created]
 118:       ,[last_edit_date]
 119:       ,[last_edit_lname]
 120:       ,[last_edit_fname]
 121:       ,[mnu_type]
 122:       ,[mnu_link]
 123:       ,[template_link]
 124:       ,[parent_id]
 125:       ,[ancestor_id]
 126:       ,[content_language]
 127:       ,[mnu_image]
 128:       ,[mnu_image_override]
 129:       ,[mnu_to_folders]
 130:       ,[mnu_to_templates]
 131:       ,[dynamic_replication_method]
 132: FROM #NewMenu 
 133: 
 134: INSERT INTO [menu_to_item_tbl]([mnu_id]
 135:       ,[item_id]
 136:       ,[item_type]
 137:       ,[item_title]
 138:       ,[item_link]
 139:       ,[item_target]
 140:       ,[order_loc]
 141:       ,[item_description]
 142:       ,[link_type]
 143:       ,[id]
 144:       ,[content_language]
 145:       ,[item_image]
 146:       ,[item_image_override])
 147: SELECT [MI].[mnu_id]
 148:       ,[item_id]
 149:       ,[item_type]
 150:       ,[item_title]
 151:       ,[item_link]
 152:       ,[item_target]
 153:       ,[order_loc]
 154:       ,[item_description]
 155:       ,[link_type]
 156:       ,[id]
 157:       ,[NM].[content_language]
 158:       ,[item_image]
 159:       ,[item_image_override]
 160: FROM [dbo].[menu_to_item_tbl] [MI]
 161: INNER JOIN #NewMenu [NM] ON [MI].mnu_id=[NM].mnu_id
 162: 
 163: */
 164:  
 165: -- INSERTED ROWS
 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 -- keep in place until 100% happy!
 187: -- COMMIT TRANSACTION -- remove comment when ready!

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Martin Jarvis
Software Developer (Senior) Freestyle Interactive Ltd
United Kingdom United Kingdom
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.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150129.1 | Last Updated 1 Nov 2011
Article Copyright 2011 by Martin Jarvis
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid