Migrating Umbraco Dictionary (MS-SQL)

Posted Monday March 4, 2013 at 11:55:21 am in Development

When working on sites with multiple languages, Umbraco developers typically use the Dictionary framework built into Umbraco. However, when working on deploying a site with a host of dictionary entries, how do we best accomplish this?

There are a few ways to accomplish this, some include utilizing existing plugins. However, if you want the SQL way of doing it, here you go:

CREATE TABLE SourceDatabase.dbo.#TempDictionary
(
	pk int NOT NULL,
	id uniqueidentifier NOT NULL,
	parent uniqueidentifier NOT NULL,
	[key] nvarchar(1000) NOT NULL
)

CREATE TABLE SourceDatabase.dbo.#TempLanguageText 
(
	pk int NOT NULL,
	languageId int NOT NULL,
	UniqueId uniqueidentifier NOT NULL,
	value nvarchar(1000) NOT NULL
)

INSERT INTO SourceDatabase.dbo.#TempDictionary (pk, id, parent, [key])
(SELECT pk, id, parent, [key]
FROM SourceDatabase.dbo.cmsDictionary AS cmsD 
WHERE cmsD.[key] collate Latin1_General_CI_AS_KS_WS NOT IN
(SELECT cmsD2.[key] 
FROM TargetDatabase.dbo.cmsDictionary AS cmsD2))

INSERT INTO SourceDatabase.dbo.#TempLanguageText (pk, languageId, UniqueId, value)
(SELECT pk, languageId, UniqueId, value
FROM SourceDatabase.dbo.cmsLanguageText AS cmsL 
WHERE cmsL.[UniqueId] IN (SELECT td.id FROM SourceDatabase.dbo.#TempDictionary AS td))

INSERT INTO TargetDatabase.dbo.cmsDictionary (id, parent, [key])
(SELECT id, parent, [key] FROM SourceDatabase.dbo.#TempDictionary)

INSERT INTO TargetDatabase.dbo.cmsLanguageText (languageId, UniqueId, value)
(SELECT languageId, UniqueId, value FROM SourceDatabase.dbo.#TempLanguageText)

DROP TABLE SourceDatabase.dbo.[#TempDictionary]
DROP TABLE SourceDatabase.dbo.[#TempLanguageText];

Just swap out SourceDatabase with your source database name and swap TargetDatabase with your target database name.

That's for MS-SQL.

This may require an application pool recycle for the changes to be picked up by Umbraco.

The opinions expressed herein are my own personal opinions and do not represent my employer’s view