Update CMS Script
From Logic Wiki
PrintLargeText_SP.sql
/************************************************ LARGE TEXT PRINT WORKAROUND *************************************************/ --IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PrintLargeText') AND type IN ( N'P', N'PC' ) ) --BEGIN -- DROP PROCEDURE PrintLargeText --END CREATE PROCEDURE [dbo].[PrintLargeText] @TextToPrint nvarchar(MAX) AS BEGIN DECLARE @String NVARCHAR(MAX); SET @String = @TextToPrint; DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */ DECLARE @offset tinyint; /*tracks the amount of offset needed */ set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10)) WHILE LEN(@String) > 1 BEGIN IF CHARINDEX(CHAR(10), @String) between 1 AND 4000 BEGIN SET @CurrentEnd = CHARINDEX(char(10), @String) -1 set @offset = 2 END ELSE BEGIN SET @CurrentEnd = 4000 set @offset = 1 END PRINT SUBSTRING(@String, 1, @CurrentEnd) set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String)) END /*End While loop*/ END;
Updater.sql
DECLARE @DeleteBeforeWrite BIT;
DECLARE @DeleteAboveIdentityStartAt BIT;
DECLARE @TargetApplicationId INT;
DECLARE @IdentityStartAt INT
DECLARE @RecordCounter INT = 0;
DECLARE @LongText NVARCHAR(MAX);
/***************************************************
Please set these 3 variables below
***************************************************/
SET @TargetApplicationId = 10;
SET @DeleteBeforeWrite = 1 ; -- If you want to delete existing records of given application ID in target database change this to 1
SET @DeleteAboveIdentityStartAt = 0;
SET @IdentityStartAt = 100000;
USE VDSCore -- Change Database name if necessary
/**************************************************/
BEGIN /* Author table variables */
DECLARE @AutAuthorId bigint ;
DECLARE @AutFirstname nvarchar(100) ;
DECLARE @AutLastname nvarchar(100) ;
DECLARE @AutImageId bigint ;
DECLARE @AutBio nvarchar(MAX) ;
DECLARE @AutSlug nvarchar(50) ;
DECLARE @AutApplicationId bigint ;
DECLARE @AutCreatedById bigint ;
DECLARE @AutDateCreated datetime ;
DECLARE @AutUpdatedById bigint ;
DECLARE @AutDateUpdated datetime ;
DECLARE @AutIsDeleted bit ;
END
BEGIN /* Comments */
DECLARE @ComCommentId bigint ;
DECLARE @ComNewsId bigint ;
DECLARE @ComDate datetime ;
DECLARE @ComName nvarchar(100) ;
DECLARE @ComEmail nvarchar(250) ;
DECLARE @ComBody nvarchar(MAX) ;
DECLARE @ComApproved bit ;
DECLARE @ComApplicationId bigint ;
DECLARE @ComCreatedById bigint ;
DECLARE @ComDateCreated datetime ;
DECLARE @ComUpdatedById bigint ;
DECLARE @ComDateUpdated datetime ;
DECLARE @ComIsDeleted bit ;
END
BEGIN /* NewsItem */
DECLARE @NINewsItemId bigint ;
DECLARE @NINewsId bigint ;
DECLARE @NIType int ;
DECLARE @NIRevealTitle nvarchar(100) ;
DECLARE @NIRevealOpen bit ;
DECLARE @NIData nvarchar(MAX) ;
DECLARE @NIPosition int ;
DECLARE @NIApplicationId bigint ;
DECLARE @NICreatedById bigint ;
DECLARE @NIDateCreated datetime ;
DECLARE @NIUpdatedById bigint ;
DECLARE @NIDateUpdated datetime ;
DECLARE @NIIsDeleted bit ;
END
BEGIN /* News */
DECLARE @NewsNewsId bigint ;
DECLARE @NewsAuthorId bigint ;
DECLARE @NewsCategoryId bigint ;
DECLARE @NewsTitle nvarchar(100) ;
DECLARE @NewsSummary nvarchar(1000) ;
DECLARE @NewsPublished datetime ;
DECLARE @NewsTags nvarchar(MAX) ;
DECLARE @NewsSlug nvarchar(50) ;
DECLARE @NewsImageId bigint ;
DECLARE @NewsIsPublished bit ;
DECLARE @NewsApplicationId bigint ;
DECLARE @NewsCreatedById bigint ;
DECLARE @NewsDateCreated datetime ;
DECLARE @NewsUpdatedById bigint ;
DECLARE @NewsDateUpdated datetime ;
DECLARE @NewsIsDeleted bit ;
END
BEGIN /*Content*/
DECLARE @ContentId bigint ;
DECLARE @ParentId bigint ;
DECLARE @Title nvarchar(100) ;
DECLARE @NavTitle nvarchar(50) ;
DECLARE @SubTitle nvarchar(1000) ;
DECLARE @Slug nvarchar(100) ;
DECLARE @IsPublic bit ;
DECLARE @ImageId bigint ;
DECLARE @BannerImageId bigint ;
DECLARE @BrowserTitle nvarchar(1000) ;
DECLARE @MetaDescription nvarchar(MAX) ;
DECLARE @MetaKeywords nvarchar(1000) ;
DECLARE @DoNotIndex bit ;
DECLARE @Position int ;
DECLARE @IsMenuItem bit ;
DECLARE @IsPublished bit ;
DECLARE @ApplicationId bigint ;
DECLARE @CreatedById bigint ;
DECLARE @DateCreated datetime ;
DECLARE @UpdatedById bigint ;
DECLARE @DateUpdated datetime ;
DECLARE @IsDeleted bit ;
END
BEGIN /*contentItem */
DECLARE @CI_ContentItemId bigint ;
DECLARE @CI_ContentId bigint ;
DECLARE @CI_Type int ;
DECLARE @CI_RevealTitle nvarchar(100) ;
DECLARE @CI_RevealOpen bit ;
DECLARE @CI_Data nvarchar(MAX) ;
DECLARE @CI_Position int ;
DECLARE @CI_ApplicationId bigint ;
DECLARE @CI_CreatedById bigint ;
DECLARE @CI_DateCreated datetime ;
DECLARE @CI_UpdatedById bigint ;
DECLARE @CI_DateUpdated datetime ;
DECLARE @CI_IsDeleted bit ;
END
BEGIN /*ContentSideBarItem table variables*/
DECLARE @CSB_ContentSideBarItemId bigint ;
DECLARE @CSB_ContentId bigint ;
DECLARE @CSB_SideBarItemId bigint ;
DECLARE @CSB_Position int ;
DECLARE @CSB_ApplicationId bigint ;
DECLARE @CSB_CreatedById bigint ;
DECLARE @CSB_DateCreated datetime ;
DECLARE @CSB_UpdatedById bigint ;
DECLARE @CSB_DateUpdated datetime ;
DECLARE @CSB_IsDeleted bit ;
END
BEGIN /*Image table variables */
DECLARE @ImgImageId bigint ;
DECLARE @ImgTitle nvarchar(50) ;
DECLARE @ImgCategoryId bigint ;
DECLARE @ImgFilePath nvarchar(1000) ;
DECLARE @ImgCaption nvarchar(MAX) ;
DECLARE @ImgWidth int ;
DECLARE @ImgHeight int ;
DECLARE @ImgFileSize bigint ;
DECLARE @ImgApplicationId bigint ;
DECLARE @ImgCreatedById bigint ;
DECLARE @ImgDateCreated datetime ;
DECLARE @ImgUpdatedById bigint ;
DECLARE @ImgDateUpdated datetime ;
DECLARE @ImgIsDeleted bit ;
END
BEGIN /* Variables table variable */
DECLARE @VarVariableId bigint ;
DECLARE @VarTitle nvarchar(50) ;
DECLARE @VarType int ;
DECLARE @VarValue nvarchar(MAX) ;
DECLARE @VarApplicationId bigint ;
DECLARE @VarCreatedById bigint ;
DECLARE @VarDateCreated datetime ;
DECLARE @VarUpdatedById bigint ;
DECLARE @VarDateUpdated datetime ;
DECLARE @VarIsDeleted bit ;
END
BEGIN /* Categories */
DECLARE @CatCategoryId bigint ;
DECLARE @CatTitle nvarchar(50) ;
DECLARE @CatType char(1) ;
DECLARE @CatSlug nvarchar(50) ;
DECLARE @CatApplicationId bigint ;
DECLARE @CatCreatedById bigint ;
DECLARE @CatDateCreated datetime ;
DECLARE @CatUpdatedById bigint ;
DECLARE @CatDateUpdated datetime ;
DECLARE @CatIsDeleted bit ;
END
BEGIN /*Resources */
DECLARE @ResResourceId bigint ;
DECLARE @ResCategoryId bigint ;
DECLARE @ResTitle nvarchar(100) ;
DECLARE @ResSummary nvarchar(MAX) ;
DECLARE @ResFilePath nvarchar(500) ;
DECLARE @ResFileType nvarchar(10) ;
DECLARE @ResFileSize bigint ;
DECLARE @ResApplicationId bigint ;
DECLARE @ResCreatedById bigint ;
DECLARE @ResDateCreated datetime ;
DECLARE @ResUpdatedById bigint ;
DECLARE @ResDateUpdated datetime ;
DECLARE @ResIsDeleted bit ;
END
BEGIN /* SideBarItem */
DECLARE @SbSideBarItemId bigint ;
DECLARE @SbTitle nvarchar(100) ;
DECLARE @SbBody nvarchar(1000) ;
DECLARE @SbLinkUrl nvarchar(500) ;
DECLARE @SbHtml nvarchar(MAX) ;
DECLARE @SbType int ;
DECLARE @SbImageId bigint ;
DECLARE @SbApplicationId bigint ;
DECLARE @SbCreatedById bigint ;
DECLARE @SbDateCreated datetime ;
DECLARE @SbUpdatedById bigint ;
DECLARE @SbDateUpdated datetime ;
DECLARE @SbIsDeleted bit ;
END
/******************************/
PRINT 'DECLARE @ID INT;'
PRINT 'DECLARE @CurrentIdentitySeed INT;'
PRINT 'SET XACT_ABORT ON'
PRINT 'BEGIN TRANSACTION UpdateCms'
IF (@DeleteBeforeWrite = 1)
BEGIN
--PRINT 'UPDATE [cms_Author] SET ImageId = NULL WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
--PRINT 'DELETE FROM cms_Comment WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
--PRINT 'DELETE FROM cms_NewsItem WHERE NewsId IN (SELECT NewsId FROM cms_News WHERE ApplicationId = 1 ' + CONVERT(VARCHAR(50), @TargetApplicationId) +')';
--PRINT 'DELETE FROM cms_News WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
PRINT 'DELETE FROM [cms_ContentItem] WHERE ContentId IN (SELECT ContentId FROM [cms_Content] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ')';
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND ContentItemId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'DELETE FROM [cms_ContentSideBarItem] WHERE ContentId IN (SELECT ContentId FROM [cms_Content] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ')';
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND ContentSideBarItemId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'DELETE FROM [cms_Content] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND ContentId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'UPDATE cms_Author SET ImageId = null WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
PRINT 'UPDATE cms_News SET ImageId = null WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
PRINT 'UPDATE cms_SideBarItem SET ImageId = null WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
PRINT 'ALTER TABLE [dbo].[cms_Author] DROP CONSTRAINT [FK_Author_Image]'
PRINT 'DELETE FROM [cms_Image] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId)
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND ImageId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'ALTER TABLE [dbo].[cms_Author] WITH NOCHECK ADD CONSTRAINT [FK_Author_Image] FOREIGN KEY([ImageId]) REFERENCES [dbo].[cms_Image] ([ImageId])'
PRINT 'ALTER TABLE [dbo].[cms_Author] CHECK CONSTRAINT [FK_Author_Image]'
PRINT 'DELETE FROM [cms_Variable] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND VariableId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'DELETE FROM [cms_Category] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ' AND CategoryId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND CategoryId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'DELETE FROM [cms_Resource] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND ResourceId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
PRINT 'DELETE FROM [cms_SideBarItem] WHERE ApplicationId = ' + CONVERT(VARCHAR(50), @TargetApplicationId);
IF (@DeleteAboveIdentityStartAt = 1)
BEGIN
PRINT ' AND SideBarItemId >= ' + CONVERT(VARCHAR(50), @IdentityStartAt);
END
END
/******************
Category
******************/
BEGIN
DECLARE category_cursor CURSOR FAST_FORWARD
FOR SELECT CategoryId, Title, Type, Slug, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_Category] WHERE ApplicationId = @TargetApplicationId
OPEN category_cursor
FETCH NEXT FROM category_cursor INTO @CatCategoryId, @CatTitle, @CatType, @CatSlug, @CatApplicationId, @CatCreatedById, @CatDateCreated, @CatUpdatedById, @CatDateUpdated, @CatIsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(CategoryId) FROM cms_Category ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_SideBarItem] OFF';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_ContentSideBarItem] OFF';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Category] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @VarVariableId) + ' - ' + @VarTitle;
PRINT 'INSERT [dbo].[cms_Category] ([CategoryId], [Title], [Type], [Slug], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES ( ' + CONVERT(VARCHAR(50), @CatCategoryId + @IdentityStartAt) + ',''' + @CatTitle + ''',''' + @CatType + ''', ''' + @CatSlug + ''',' +
CONVERT(VARCHAR(50), @CatApplicationId) + ', ' +
CASE
WHEN @CatCreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CatCreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @CatDateCreated) + ''' AS DateTime), ' +
CASE
WHEN @CatUpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CatUpdatedById)
END
+
CASE
WHEN @CatDateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @CatDateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @CatIsDeleted) + ');';
FETCH NEXT FROM category_cursor INTO @CatCategoryId, @CatTitle, @CatType, @CatSlug, @CatApplicationId, @CatCreatedById, @CatDateCreated, @CatUpdatedById, @CatDateUpdated, @CatIsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Category] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_Category'', reseed, @CurrentIdentitySeed)';
CLOSE category_cursor;
DEALLOCATE category_cursor;
END
/******************
Image
******************/
BEGIN
DECLARE image_cursor CURSOR FAST_FORWARD
FOR SELECT ImageId, Title, CategoryId, FilePath, Caption, Width, Height, FileSize, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_Image] WHERE ApplicationId = @TargetApplicationId
OPEN image_cursor
FETCH NEXT FROM image_cursor INTO @ImgImageId, @ImgTitle, @ImgCategoryId, @ImgFilePath, @ImgCaption, @ImgWidth, @ImgHeight, @ImgFileSize, @ImgApplicationId, @ImgCreatedById, @ImgDateCreated, @ImgUpdatedById, @ImgDateUpdated, @ImgIsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(ImageId) FROM cms_Image ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Image] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @ImgImageId) + ' - ' + @ImgTitle;
PRINT 'INSERT [dbo].[cms_Image] ([ImageId], [Title], [CategoryId], [FilePath], [Caption], [Width], [Height], [FileSize], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES ( ' + CONVERT(VARCHAR(50), @ImgImageId + @IdentityStartAt) + ',''' + @ImgTitle + ''',' + CONVERT(VARCHAR(50), @ImgCategoryId + @IdentityStartAt) + ', ''' + @ImgFilePath + ''',''' +
CASE
WHEN @ImgCaption IS NULL THEN 'NULL'
ELSE @ImgCaption
END
+ ''',' +
CASE
WHEN @ImgWidth IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImgWidth)
END
+ ', ' +
CASE
WHEN @ImgHeight IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImgHeight)
END
+ ', ' +
CASE
WHEN @ImgFileSize IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImgFileSize)
END
+ ', ' +
CONVERT(VARCHAR(50), @TargetApplicationId) + ', ' +
CASE
WHEN @ImgCreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImgCreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @ImgDateCreated) + ''' AS DateTime), ' +
CASE
WHEN @ImgUpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImgUpdatedById)
END
+
CASE
WHEN @ImgDateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @ImgDateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @ImgIsDeleted) + ');';
FETCH NEXT FROM image_cursor INTO @ImgImageId, @ImgTitle, @ImgCategoryId, @ImgFilePath, @ImgCaption, @ImgWidth, @ImgHeight, @ImgFileSize, @ImgApplicationId, @ImgCreatedById, @ImgDateCreated, @ImgUpdatedById, @ImgDateUpdated, @ImgIsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Image] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_Image'', reseed, @CurrentIdentitySeed)';
CLOSE image_cursor;
DEALLOCATE image_cursor;
END
/******************
Variables
******************/
BEGIN
DECLARE variable_cursor CURSOR FAST_FORWARD
FOR SELECT VariableId, Title, Type, Value, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_Variable] WHERE ApplicationId = @TargetApplicationId
OPEN variable_cursor
FETCH NEXT FROM variable_cursor INTO @VarVariableId, @VarTitle, @VarType, @VarValue, @VarApplicationId, @VarCreatedById, @VarDateCreated, @VarUpdatedById, @VarDateUpdated, @VarIsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(VariableId) FROM cms_Variable ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Variable] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @VarVariableId) + ' - ' + @VarTitle;
PRINT 'INSERT [dbo].[cms_Variable] ([VariableId], [Title], [Type], [Value], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES ( ' + CONVERT(VARCHAR(50), @VarVariableId + @IdentityStartAt) + ',''' + @VarTitle + ''',' + CONVERT(VARCHAR(50), @VarType) + ', ''' + @VarValue + ''',' +
CONVERT(VARCHAR(50), @TargetApplicationId) + ', ' +
CASE
WHEN @VarCreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @VarCreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @VarDateCreated) + ''' AS DateTime), ' +
CASE
WHEN @VarUpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @VarUpdatedById)
END
+
CASE
WHEN @VarDateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @VarDateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @VarIsDeleted) + ');';
FETCH NEXT FROM variable_cursor INTO @VarVariableId, @VarTitle, @VarType, @VarValue, @VarApplicationId, @VarCreatedById, @VarDateCreated, @VarUpdatedById, @VarDateUpdated, @VarIsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Variable] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_Variable'', reseed, @CurrentIdentitySeed)';
CLOSE variable_cursor;
DEALLOCATE variable_cursor;
END
/******************
Resources
******************/
BEGIN
DECLARE resource_cursor CURSOR FAST_FORWARD
FOR SELECT ResourceId, categoryid, title, summary, filepath, filetype, filesize, applicationid, createdbyid, datecreated, updatedbyid, dateupdated, isdeleted
FROM [cms_Resource] WHERE ApplicationId = @TargetApplicationId
OPEN resource_cursor
FETCH NEXT FROM resource_cursor INTO @ResResourceId, @Rescategoryid, @Restitle, @Ressummary, @Resfilepath, @Resfiletype, @Resfilesize, @Resapplicationid, @Rescreatedbyid, @Resdatecreated,
@Resupdatedbyid, @Resdateupdated, @Resisdeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(ResourceId) FROM cms_Resource ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Resource] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @VarVariableId) + ' - ' + @VarTitle;
PRINT 'INSERT [dbo].[cms_Resource] ([ResourceId], [categoryid], [title], [summary], [filepath], [filetype], [filesize], [applicationid], [createdbyid], [datecreated], [updatedbyid], [dateupdated], [isdeleted])';
PRINT ' VALUES ( ' + CONVERT(VARCHAR(50), @ResResourceId + @IdentityStartAt) +
', ' + CONVERT(VARCHAR(50), @Rescategoryid + @IdentityStartAt) +
',''' + @Restitle + ''',''' + ISNULL(@Ressummary, 'NULL') + ''', ''' + @Resfilepath + ''', ''' + ISNULL(@Resfiletype, 'NULL') + ''',' +
CONVERT(VARCHAR(50), @Resfilesize) + ', ' +
CONVERT(VARCHAR(50), @Resapplicationid) + ', ' +
CASE
WHEN @Rescreatedbyid IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @Rescreatedbyid)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @Resdatecreated) + ''' AS DateTime), ' +
CASE
WHEN @Resupdatedbyid IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @Resupdatedbyid)
END
+
CASE
WHEN @Resdateupdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @Resdateupdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @Resisdeleted) + ');';
FETCH NEXT FROM resource_cursor INTO @ResResourceId, @Rescategoryid, @Restitle, @Ressummary, @Resfilepath, @Resfiletype, @Resfilesize, @Resapplicationid, @Rescreatedbyid, @Resdatecreated, @Resupdatedbyid, @Resdateupdated, @Resisdeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Resource] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_Resource'', reseed, @CurrentIdentitySeed)';
CLOSE resource_cursor;
DEALLOCATE resource_cursor;
END
/******************
Content
******************/
BEGIN
DECLARE content_cursor CURSOR FAST_FORWARD
FOR SELECT ContentId, ParentId, Title, NavTitle, SubTitle, Slug, IsPublic, ImageId, BannerImageId, BrowserTitle, MetaDescription, MetaKeywords, DoNotIndex,
Position, IsMenuItem, IsPublished, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_Content] WHERE ApplicationId = @TargetApplicationId
OPEN content_cursor
FETCH NEXT FROM content_cursor INTO @ContentId, @ParentId, @Title, @NavTitle, @SubTitle, @Slug, @IsPublic, @ImageId, @BannerImageId, @BrowserTitle, @MetaDescription, @MetaKeywords, @DoNotIndex,
@Position, @IsMenuItem, @IsPublished, @ApplicationId, @CreatedById, @DateCreated, @UpdatedById, @DateUpdated, @IsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(ContentId) FROM cms_Content ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Content] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + @Slug;
PRINT 'INSERT [dbo].[cms_Content] ([ContentId], [ParentId], [Title], [NavTitle], [SubTitle], [Slug], [IsPublic], [ImageId], [BannerImageId], [BrowserTitle], [MetaDescription], [MetaKeywords], [DoNotIndex], [Position], [IsMenuItem], [IsPublished], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES (' + CONVERT(VARCHAR(50), @ContentId + @IdentityStartAt) + ',' +
CASE
WHEN @ParentId IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ParentId + @IdentityStartAt)
END
+ ', ' +
'''' + REPLACE(@Title, '''', '''''') + ''', '''+ REPLACE(@NavTitle, '''', '''''') + ''', ''' + REPLACE(@SubTitle, '''', '''''') + ''', ''' + @Slug + ''', ' ;
PRINT CONVERT(VARCHAR(50), @IsPublic) + ', ' +
CASE
WHEN @ImageId IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @ImageId + @IdentityStartAt)
END
+ ', ' +
CASE
WHEN @BannerImageId IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @BannerImageId + @IdentityStartAt)
END
+ ', ''' +
CASE
WHEN @BrowserTitle IS NULL THEN 'NULL'
ELSE @BrowserTitle
END
+ ''', ''' +
CASE
WHEN @MetaDescription IS NULL THEN 'NULL'
ELSE @MetaDescription
END
+ ''', ''' +
CASE
WHEN @MetaKeywords IS NULL THEN 'NULL'
ELSE @MetaKeywords
END
+ ''', ' + CONVERT(VARCHAR(50), @DoNotIndex) + ', ' +
CONVERT(VARCHAR(50), @Position) + ', ' + CONVERT(VARCHAR(50), @IsMenuItem) + ', ' + CONVERT(VARCHAR(50), @IsPublished) + ', ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ', ' +
CASE
WHEN @CreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @DateCreated) + ''' AS DateTime), ' +
CASE
WHEN @UpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @UpdatedById)
END
+
CASE
WHEN @DateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @DateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @IsDeleted) + ');';
FETCH NEXT FROM content_cursor INTO @ContentId, @ParentId, @Title, @NavTitle, @SubTitle, @Slug, @IsPublic, @ImageId, @BannerImageId, @BrowserTitle, @MetaDescription, @MetaKeywords, @DoNotIndex,
@Position, @IsMenuItem, @IsPublished, @ApplicationId, @CreatedById, @DateCreated, @UpdatedById, @DateUpdated, @IsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_Content] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_Content'', reseed, @CurrentIdentitySeed)';
CLOSE content_cursor;
DEALLOCATE content_cursor;
END
/******************
ContentItem
******************/
BEGIN
DECLARE content_item_cursor CURSOR FAST_FORWARD
FOR SELECT ContentItemId, ContentId, Type, RevealTitle, RevealOpen, Data, Position, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_ContentItem] WHERE ContentId IN (SELECT ContentId FROM cms_Content WHERE ApplicationId = @ApplicationId)
OPEN content_item_cursor
FETCH NEXT FROM content_item_cursor INTO @CI_ContentItemId, @CI_ContentId, @CI_Type, @CI_RevealTitle, @CI_RevealOpen, @CI_Data, @CI_Position, @CI_ApplicationId, @CI_CreatedById, @CI_DateCreated, @CI_UpdatedById, @CI_DateUpdated, @CI_IsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(ContentItemId) FROM cms_ContentItem ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_ContentItem] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @CI_ContentItemId);
PRINT ' INSERT [dbo].[cms_ContentItem] ([ContentItemId], [ContentId], [Type], [RevealTitle], [RevealOpen], [Data], [Position], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES (' + CONVERT(VARCHAR(50), @CI_ContentItemId + @IdentityStartAt) + ', ' + CONVERT(VARCHAR(50), @CI_ContentId + @IdentityStartAt) + ', ' +
CONVERT(VARCHAR(50), @CI_Type) + ', ''' + REPLACE(@CI_RevealTitle,'''', '''''') +
''',' + CONVERT(VARCHAR(50), @CI_RevealOpen) + ', '''
SET @LongText = REPLACE(@CI_Data ,'''', '''''');
exec dbo.PrintLargeText @TextToPrint = @LongText ;
PRINT ''', ' + CONVERT(VARCHAR(50), @CI_Position) + ', ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ', ' +
CASE
WHEN @CI_CreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CI_CreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @CI_DateCreated) + ''' AS DateTime), ' +
CASE
WHEN @CI_UpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CI_UpdatedById)
END
+
CASE
WHEN @CI_DateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @CI_DateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @CI_IsDeleted) + ');';
FETCH NEXT FROM content_item_cursor INTO @CI_ContentItemId, @CI_ContentId, @CI_Type, @CI_RevealTitle, @CI_RevealOpen, @CI_Data, @CI_Position, @CI_ApplicationId, @CI_CreatedById, @CI_DateCreated, @CI_UpdatedById, @CI_DateUpdated, @CI_IsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_ContentItem] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_ContentItem'', reseed, @CurrentIdentitySeed)';
CLOSE content_item_cursor;
DEALLOCATE content_item_cursor;
PRINT 'UPDATE cms_ContentItem SET Data = SUBSTRING(Data, 1, LEN(Data)-2 ) WHERE Data LIKE ''%''+CHAR(13)+CHAR(10)';
PRINT 'UPDATE cms_ContentItem SET Data = SUBSTRING(Data, 3, LEN(Data)) WHERE Data LIKE CHAR(13)+CHAR(10)+''%''';
END
/******************
ContentSideBarItem
******************/
BEGIN
DECLARE content_sbitem_cursor CURSOR FAST_FORWARD
FOR SELECT ContentSideBarItemId, ContentId, SideBarItemId, Position, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_ContentSideBarItem] WHERE ContentId IN (SELECT ContentId FROM cms_Content WHERE ApplicationId = @ApplicationId)
OPEN content_sbitem_cursor
FETCH NEXT FROM content_sbitem_cursor INTO @CSB_ContentSideBarItemId, @CSB_ContentId, @CSB_SideBarItemId, @CSB_Position, @CSB_ApplicationId, @CSB_CreatedById, @CSB_DateCreated, @CSB_UpdatedById, @CSB_DateUpdated, @CSB_IsDeleted
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] DROP CONSTRAINT [FK_ContentSideBarItem_SideBarItem]';
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] DROP CONSTRAINT [FK_ContentSideBarItem_Content]';
PRINT 'SELECT @CurrentIdentitySeed = MAX(ContentSideBarItemId) FROM cms_ContentSideBarItem ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_ContentSideBarItem] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + CONVERT(VARCHAR(50), @CSB_ContentSideBarItemId);
PRINT ' INSERT [dbo].[cms_ContentSideBarItem] ([ContentSideBarItemId], [ContentId], [SideBarItemId], [Position], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES (' + CONVERT(VARCHAR(50), @CSB_ContentSideBarItemId + @IdentityStartAt) + ', ' + CONVERT(VARCHAR(50), @CSB_ContentId + @IdentityStartAt) + ', ' + CONVERT(VARCHAR(50), @CSB_SideBarItemId) + ', ' + CONVERT(VARCHAR(50), @CSB_Position) + ', ' + CONVERT(VARCHAR(50), @TargetApplicationId) + ', ' +
CASE
WHEN @CSB_CreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CSB_CreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @CSB_DateCreated) + ''' AS DateTime), ' +
CASE
WHEN @CSB_UpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @CSB_UpdatedById)
END
+
CASE
WHEN @CSB_DateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @CSB_DateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @CSB_IsDeleted) + ');';
FETCH NEXT FROM content_sbitem_cursor INTO @CSB_ContentSideBarItemId, @CSB_ContentId, @CSB_SideBarItemId, @CSB_Position, @CSB_ApplicationId, @CSB_CreatedById, @CSB_DateCreated, @CSB_UpdatedById, @CSB_DateUpdated, @CSB_IsDeleted
END
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] WITH CHECK ADD CONSTRAINT [FK_ContentSideBarItem_Content] FOREIGN KEY([ContentId]) REFERENCES [dbo].[cms_Content] ([ContentId])';
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] CHECK CONSTRAINT [FK_ContentSideBarItem_Content]';
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] WITH NOCHECK ADD CONSTRAINT [FK_ContentSideBarItem_SideBarItem] FOREIGN KEY([SideBarItemId]) REFERENCES [dbo].[cms_SideBarItem] ([SideBarItemId])';
PRINT 'ALTER TABLE [dbo].[cms_ContentSideBarItem] CHECK CONSTRAINT [FK_ContentSideBarItem_SideBarItem]';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_ContentSideBarItem] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_ContentSideBarItem'', reseed, @CurrentIdentitySeed)';
CLOSE content_sbitem_cursor;
DEALLOCATE content_sbitem_cursor;
END
/******************
SideBarItem
******************/
BEGIN
DECLARE sbitem_cursor CURSOR FAST_FORWARD
FOR SELECT SideBarItemId, Title, Body, LinkUrl, Html, Type, ImageId, ApplicationId, CreatedById, DateCreated, UpdatedById, DateUpdated, IsDeleted
FROM [cms_SideBarItem] WHERE ApplicationId = @ApplicationId
OPEN sbitem_cursor
FETCH NEXT FROM sbitem_cursor INTO @SbSideBarItemId, @SbTitle, @SbBody, @SbLinkUrl, @SbHtml, @SbType, @SbImageId, @SbApplicationId, @SbCreatedById, @SbDateCreated, @SbUpdatedById, @SbDateUpdated, @SbIsDeleted
PRINT 'SELECT @CurrentIdentitySeed = MAX(SideBarItemId) FROM cms_SideBarItem ';
PRINT 'SET IDENTITY_INSERT [dbo].[cms_SideBarItem] ON';
SET @RecordCounter = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordCounter = @RecordCounter + 1;
PRINT '--' + CONVERT(VARCHAR(50), @RecordCounter) + ' : ' + @SbTitle;
PRINT ' INSERT [dbo].[cms_SideBarItem] ([SideBarItemId], [Title], [Body], [LinkUrl], [Html], [Type], [ImageId], [ApplicationId], [CreatedById], [DateCreated], [UpdatedById], [DateUpdated], [IsDeleted])';
PRINT ' VALUES (' + CONVERT(VARCHAR(50), @SbSideBarItemId + @IdentityStartAt) + ', ''' + REPLACE(@SbTitle ,'''', '''''') + ''', ''' + REPLACE(@SbBody ,'''', '''''') + ''', ''' +
ISNULL(@SbLinkUrl, 'NULL') + ''', ''' +
ISNULL(@SbHtml, 'NULL')
+ ''',' + CONVERT(VARCHAR(50), @SbType) + ', ' +
CASE
WHEN @SbImageId IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @SbImageId + @IdentityStartAt)
END
+ ', ' + CONVERT(VARCHAR(50), @SbApplicationId) + ', ' +
CASE
WHEN @SbCreatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @SbCreatedById)
END
+ ', CAST(N''' + CONVERT(VARCHAR(50), @SbDateCreated) + ''' AS DateTime), ' +
CASE
WHEN @SbUpdatedById IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(50), @SbUpdatedById)
END
+
CASE
WHEN @SbDateUpdated IS NULL THEN ', NULL '
ELSE ', CAST(N''' + CONVERT(VARCHAR(50), @SbDateUpdated) + ''' AS DateTime) '
END
+ ', ' + CONVERT(VARCHAR(50), @SbIsDeleted) + ');';
FETCH NEXT FROM sbitem_cursor INTO @SbSideBarItemId, @SbTitle, @SbBody, @SbLinkUrl, @SbHtml, @SbType, @SbImageId, @SbApplicationId, @SbCreatedById, @SbDateCreated, @SbUpdatedById, @SbDateUpdated, @SbIsDeleted
END
PRINT 'SET IDENTITY_INSERT [dbo].[cms_SideBarItem] OFF';
PRINT 'SET @CurrentIdentitySeed = @CurrentIdentitySeed + 1';
PRINT 'DBCC checkident (''cms_SideBarItem'', reseed, @CurrentIdentitySeed)';
CLOSE sbitem_cursor;
DEALLOCATE sbitem_cursor;
END
/******************************/
PRINT 'COMMIT TRAN UpdateCms'
