Rory Primrose

-- TODO 
-- Replace [AccountId] with your account id in the site 
-- Replace [AccountName] with your account name in the site 
-- Replace [NewDatabaseName] with your new database name 
-- Replace [OldDatabaseName] with your old database name 
-- Replace [DomainName] with your domain name 
-- Set @BlogSectionId to your section id

USE [NewDatabaseName] 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_Posts 
ADD CSPostID INT NULL 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_categories 
ADD CSCategoryID INT NULL 
GO 

-- Posts and Comments ------------------------------- 

DECLARE @BlogSectionId INT 
SET @BlogSectionId = 3 

-- Insert pages 
INSERT INTO [NewDatabaseName].dbo.be_Pages 
( 
    PageID, 
    Title, 
    [Description], 
    PageContent, 
    Keywords, 
    DateCreated, 
    DateModified, 
    IsPublished, 
    IsFrontPage, 
    Parent, 
    ShowInList 
) 
SELECT NEWID(), 
    [Subject], 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('Excerpt', c.PropertyNames, c.PropertyValues)), 
    Body, 
    NULL, 
    PostDate, 
    PostDate, 
    1, 
    0, 
    NULL, 
    1 
FROM [OldDatabaseName].dbo.cs_Posts c 
WHERE SectionID = @BlogSectionId 
    AND PostLevel = 1 
    AND ApplicationPostType = 2 

-- Insert posts 
INSERT INTO [NewDatabaseName].dbo.be_Posts 
( 
    PostID, 
    Title, 
    [Description], 
    PostContent, 
    DateCreated, 
    DateModified, 
    Author, 
    IsPublished, 
    IsCommentEnabled, 
    Slug, 
    CSPostID 
) 
SELECT NEWID(), 
    [Subject], 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('Excerpt', c.PropertyNames, c.PropertyValues)), 
    Body, 
    PostDate, 
    PostDate, 
    '[AccountId]', 
    1, 
    1, 
    CASE WHEN ISNULL(PostName, '') = '' THEN 
        CAST(PostID AS NVARCHAR(255)) 
    ELSE 
        PostName 
    END, 
    PostID 
FROM [OldDatabaseName].dbo.cs_Posts c 
WHERE SectionID = @BlogSectionId 
    AND PostLevel = 1 
    AND ApplicationPostType = 1 

-- Insert comments 
INSERT INTO [NewDatabaseName].dbo.be_PostComment 
( 
    PostID, 
    CommentDate, 
    Author, 
    Email, 
    Website, 
    Comment, 
    Ip, 
    IsApproved 
) 
SELECT b.PostID, 
    c.PostDate, 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('SubmittedUserName', c.PropertyNames, c.PropertyValues)), 
    'nobody@[DomainName]', 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('TitleUrl', c.PropertyNames, c.PropertyValues)) , 
    c.Body, 
    c.IPAddress, 
    1 
FROM [NewDatabaseName].dbo.be_Posts b 
    INNER JOIN [OldDatabaseName].dbo.cs_posts c ON b.CSPostID = c.ParentID 
WHERE c.SectionId = @BlogSectionId 
    AND c.PostLevel = 2 
    AND c.PostType = 1 
    AND c.ApplicationPostType <> 8 

-- Insert trackbacks 
INSERT INTO [NewDatabaseName].dbo.be_PostComment 
( 
    PostID, 
    CommentDate, 
    Author, 
    Email, 
    Website, 
    Comment, 
    Ip, 
    IsApproved 
) 
SELECT b.PostID, 
    c.PostDate, 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('trackbackName', c.PropertyNames, c.PropertyValues)), 
    'trackback', 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('TitleUrl', c.PropertyNames, c.PropertyValues)) , 
    'Trackback from ' + ([OldDatabaseName].dbo.FetchExtendendAttributeValue('trackbackName', c.PropertyNames, c.PropertyValues)) + '\n\n' + CAST(c.Body AS NVARCHAR(MAX)), 
    c.IPAddress, 
    1 
FROM [NewDatabaseName].dbo.be_Posts b 
    INNER JOIN [OldDatabaseName].dbo.cs_posts c ON b.CSPostID = c.ParentID 
WHERE c.SectionId = @BlogSectionId 
    AND c.PostLevel = 2 
    AND c.PostType = 1 
    AND c.ApplicationPostType = 8 

UPDATE [NewDatabaseName].dbo.be_PostComment 
SET Author = '[AccountName]' 
WHERE Author IS NULL 

-- Categories -------------------------------------------- 

INSERT INTO [NewDatabaseName].dbo.be_categories 
( 
    CategoryName, 
    CSCategoryID 
) 
SELECT [Name], 
    CategoryID 
FROM [OldDatabaseName].dbo.cs_post_categories c 
WHERE c.IsEnabled = 1 
    AND SectionID = @BlogSectionId 

INSERT INTO [NewDatabaseName].dbo.be_postcategory 
( 
    PostID, 
    CategoryID 
) 
SELECT b.postID, 
    bc.CategoryID 
FROM [NewDatabaseName].dbo.be_categories bc 
    INNER JOIN [OldDatabaseName].dbo.cs_post_categories c ON c.CategoryID = bc.CSCategoryID 
    INNER JOIN  [OldDatabaseName].dbo.cs_posts_incategories cic ON c.CategoryID = cic.CategoryID 
    INNER JOIN  [OldDatabaseName].dbo.cs_posts p ON cic.PostId = p.PostId 
    INNER JOIN [NewDatabaseName].dbo.be_posts b ON b.CSPostID = p.postID 

GO 

-- Clean up 

ALTER TABLE [NewDatabaseName].dbo.be_Posts 
DROP COLUMN CSPostID 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_categories 
DROP COLUMN CSCategoryID 
GO