Dan Bartels

Declare @SectionID int
Set @SectionID = 4

Declare @SettingsID int
set @SettingsID = 1000

Declare @UserName varchar(50) --From the aspnet_Users table
set @UserName = 'DanB'

Declare @UserID int
Select @UserID = cs_UserProfile.UserID from aspnet_Users join cs_UserProfile on aspnet_Users.UserID =  cs_UserProfile.MembershipID where UserName = @UserName

Declare @AnonUserName varchar(50)
set @AnonUserName = 'Anonymous'

Declare @AnonUserID int
Select @UserID = cs_UserProfile.UserID from aspnet_Users join cs_UserProfile on aspnet_Users.UserID =  cs_UserProfile.MembershipID where UserName = @AnonUserName



SET IDENTITY_INSERT cs_LinkCategories ON
INSERT INTO [CommunityServer].[dbo].[cs_LinkCategories](LinkCategoryID, [SectionID], [Name], [Description], [IsEnabled], [SortOrder], [SettingsID])
SELECT [CategoryID], @SectionID, [Title], [Description], [Active], 0, @SettingsID FROM [BLOG].[dbo].[blog_LinkCategories] WHERE CategoryType = 0

--Change the URL field in the cs_links to 255 (100 characters might not be enough)

SET IDENTITY_INSERT cs_Links ON
INSERT INTO [CommunityServer].[dbo].[cs_Links]([LinkID], [LinkCategoryID], [Title], [Url], [IsEnabled], [SortOrder], [SettingsID])
SELECT [LinkID], [CategoryID], [Title], [Url], [Active], 0, @SettingsID FROM [BLOG].[dbo].[blog_Links] WHERE Title is not null
GO

SET IDENTITY_INSERT cs_Post_Categories ON
INSERT INTO [CommunityServer].[dbo].[cs_Post_Categories]([CategoryID], [SectionID], [Name], [CategoryType], [IsEnabled], [ParentID], [Description], [SettingsID], [Path])
SELECT [CategoryID], @SectionID, [Title],
CASE [CategoryType]
        WHEN 1
            THEN 2
        ELSE 3
END AS [CategoryType]
, [Active], 0, [Description], @SettingsID, '/' FROM [BLOG].[dbo].[blog_LinkCategories] WHERE CategoryType in (1,2)

SET IDENTITY_INSERT cs_Threads ON
INSERT INTO [CommunityServer].[dbo].[cs_Threads]([ThreadID], [SectionID], [UserID], [PostAuthor], [PostDate], [ThreadDate], [LastViewedDate], [StickyDate], [TotalViews], [TotalReplies], [MostRecentPostAuthorID], [MostRecentPostAuthor], [MostRecentPostID], [IsLocked], [IsSticky], [IsApproved], [RatingSum], [TotalRatings], [ThreadEmoticonID], [ThreadStatus], [SettingsID])
SELECT C.[ID], @SectionID,@UserID ,@UserName, C.[DateAdded], C.[DateAdded], C.[DateAdded], C.[DateAdded], 0 as views, isnull(CountID,0) as FeedbackCount, isnull(FeedbackAuthorID,@UserID ) as LastPosterID, isnull(FeedbackAuthor, @UserName) as LastPosterName, isnull(LatestPost.ID, C.ID) as LastPostID, 0, 0 ,1, 0, 0, 0, 0, @SettingsID
FROM [BLOG].[dbo].[blog_Content] C
LEFT OUTER JOIN (SELECT [ID], CountID, LatestPostIndex.ParentID, Author, DateAdded, @AnonUserID as FeedbackAuthorID, 'Anonymous' as FeedbackAuthor FROM
(SELECT Max([ID]) as MaxID, Count([ID]) as CountID, ParentID FROM [BLOG].[dbo].[blog_Content] where ParentID <> -1 Group by ParentID) LatestPostIndex
join (SELECT [ID], [Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName] FROM [BLOG].[dbo].[blog_Content]) Posts
on LatestPostIndex.MaxID = Posts.ID) LatestPost
ON C.ID = LatestPost.ParentID
Where PostType in (1,2)
GO

SET IDENTITY_INSERT cs_Posts ON
INSERT INTO [CommunityServer].[dbo].[cs_Posts]([PostID], [ThreadID], [ParentID], [PostAuthor], [UserID], [SectionID], [PostLevel], [SortOrder], [Subject], [PostDate], [IsApproved], [IsLocked], [IsIndexed], [TotalViews], [Body], [FormattedBody], [IPAddress], [PostType], [EmoticonID], [PropertyNames], [PropertyValues], [SettingsID], [AggViews])
SELECT [ID],
CASE [ParentID]
        WHEN -1
            THEN [ID]
        ELSE [ParentID] 
END AS [ThreadID],
CASE [ParentID]
        WHEN -1
            THEN [ID]
        ELSE [ParentID] 
END AS [ParentID],
CASE [ParentID]
        WHEN -1
            THEN @UserName
        ELSE @AnonUserName
END AS [Author],
CASE [ParentID]
        WHEN -1
            THEN @UserID
        ELSE @AnonUserID
END AS [UserID], 
@SectionID,
CASE [ParentID]
        WHEN -1
            THEN 1
        ELSE 2
END AS [Level], 
0 as SortOrder,
[Title], [DateAdded], 1, 0, 0, isnull(WebCount,0), [Text],[Text], isnull([SourceName],'127.0.0.1') as IP, 1,0,

--Note the 4 below might be related to the site ID, I am not sure where the other numbers
--are coming from, you should do a sample post and make a followup to be sure the numbers match
CASE [ParentID] 
        WHEN -1
            THEN 'EnableRatings:S:0:4:EnableTrackBacks:S:4:4:EverPublished:S:8:4:'
        ELSE 'SubmittedUserName:S:0:11:EverPublished:S:11:4:'
END AS [PropertyNames], 
CASE [ParentID]
        WHEN -1
            THEN 'TrueTrueTrue'
        ELSE [Author] + 'True'
END AS [PropertyValues], 
@SettingsID,
isnull(AggCount,0) FROM [BLOG].[dbo].[blog_Content] LEFT OUTER JOIN [BLOG].[dbo].[blog_EntryViewCount] on [BLOG].[dbo].[blog_Content].[ID] = [BLOG].[dbo].[blog_EntryViewCount].[EntryID]
GO


INSERT INTO [CommunityServer].[dbo].[cs_weblog_Posts]([PostID], [PostName], [Excerpt], [TitleUrl], [PostConfig], [BlogPostType], [SettingsID], [BloggerTime])
SELECT [ID], [EntryName], [Description], [TitleUrl],
CASE [ParentID]
        WHEN -1
            THEN 9
        ELSE 0 
END AS [PostConfig],
CASE [ParentID]
        WHEN -1
            THEN 1
        ELSE 4 
END AS [BlogPostType],
@SettingsID,[DateAdded] FROM [BLOG].[dbo].[blog_Content]
GO

INSERT INTO [CommunityServer].[dbo].[cs_Posts_InCategories]([CategoryID], [PostID], [SettingsID])
SELECT [CategoryID], [PostID], @SettingsID FROM [BLOG].[dbo].[blog_Links] where PostID <> -1
GO

UPDATE [CommunityServer].[dbo].[cs_weblog_Posts] SET BlogPostType = 2 FROM [CommunityServer].[dbo].[cs_weblog_Posts] wp JOIN [CommunityServer].[dbo].[cs_Posts_InCategories] pic ON wp.PostID = pic.PostID JOIN [CommunityServer].[dbo].[cs_Post_Categories] pc ON pic.CategoryID = pc.CategoryID WHERE CategoryType = 3 AND BlogPostType <> 4
GO