View source for Update CMS Script

[[Category:SQL]]

== PrintLargeText_SP.sql ==
<pre class="brush: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; 

<pre>

== Updater.sql ==
<pre class="brush: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'
<pre>