把旧论坛（动网）数据导入Asp.NET Forums

/*用户表*/
/*litbbs.dbo.[user]移植至Forums.dbo.forums_Users */
SET IDENTITY_INSERT Forums.dbo.forums_Users ON
insert into Forums.dbo.forums_Users
 (
 UserID,
 UserName,
 Password,
 Email,
 PasswordFormat,
 UserAccountStatus,
    NickName
 )
select 
 k as UserID
 ,name as UserName
 ,password as Password
 ,Email
    ,1 as PasswordFormat
 ,1 as UserAccountStatus
 ,name as NickName
from litbbs.dbo.[user]
SET IDENTITY_INSERT Forums.dbo.forums_Users OFF

/*用户表*/
/*litbbs.dbo.[user]移植至Forums.dbo.forums_Users */
SET IDENTITY_INSERT Forums.dbo.forums_UserProfile ON
insert into Forums.dbo.forums_UserProfile
(
UserID,
TimeZone,
TotalPosts,
PostSortOrder,
IsAvatarApproved,
ModerationLevel,
EnableThreadTracking,
EnableDisplayUnreadThreadsOnly,
EnableAvatar,
EnableDisplayInMemberList,
EnablePrivateMessages,
EnableOnlineStatus,
EnableHtmlEmail,
TotalValuedPosts,
Integral
)
select 
k as UserID,
1 as TimeZone,
1 as TotalPosts,
1 as PostSortOrder,
'' as StringNameValues,
'' as PostRank,
'' as EnableThreadTracking,
'' as EnableDisplayUnreadThreadsOnly,
'' as EnableAvatar,
'' as EnableDisplayInMemberList,
'' as EnablePrivateMessages,
'' as EnableOnlineStatus,
'' as EnableHtmlEmail,
1 as TotalValuedPosts,
1 as Integral
from litbbs.dbo.[user]
SET IDENTITY_INSERT Forums.dbo.forums_UserProfile OFF

/*forums_Threads表*/
/*litbbs.dbo.main移植至Forums.dbo.forums_Threads */
SET IDENTITY_INSERT Forums.dbo.forums_Threads ON
insert into Forums.dbo.forums_Threads
(
ThreadID,
ForumID,
UserID,
PostAuthor,
PostDate,
ThreadDate,
LastViewedDate,
StickyDate,
TotalViews,
TotalReplies,
MostRecentPostAuthorID,
MostRecentPostAuthor,
MostRecentPostID,
IsLocked,
IsSticky,
IsApproved
)
select 
id   as ThreadID
,34 as ForumID
,UserID as UserID
,name as PostAuthor
,date as PostDate
,date as ThreadDate
,date as LastViewedDate
,date as StickyDate
,hit as TotalViews
,re as TotalReplies
,UserID as MostRecentPostAuthorID
,name as MostRecentPostAuthor
,'3028' as MostRecentPostID
,'false' as IsLocked
,'false' as IsSticky
,'true' as IsApproved
from litbbs.dbo.main where bbs=5
SET IDENTITY_INSERT Forums.dbo.forums_Threads OFF

 

/*forums_Posts表*/
/*litbbs.dbo.main移植至Forums.dbo.forums_Posts */
SET IDENTITY_INSERT Forums.dbo.forums_Posts ON
insert into Forums.dbo.forums_Posts
(
PostID,
ThreadID,
ParentID,
PostAuthor,
UserID,
ForumID,
PostLevel,
SortOrder,
Subject,
PostDate,
IsApproved,
IsLocked,
IsIndexed,
TotalViews,
Body,
FormattedBody,
IPAddress,
PostType
)
select 
 id as PostID
 ,id as ThreadID
 ,id as ParentID
 ,name as PostAuthor
 ,userid as UserID
 ,'34' as ForumID
    ,'1' as PostLevel
 ,level as SortOrder
    ,title as Subject
    ,date as PostDate
 ,'true' as IsApproved
 ,'false' as IsLocked
 ,'false' as IsIndexed
    ,hit as TotalViews
 ,text    as Body
 ,text    as FormattedBody
 ,ip as IPAddress
 ,'1' as PostType
from litbbs.dbo.main where bbs=5 order by id asc
SET IDENTITY_INSERT Forums.dbo.forums_Posts OFF

 

/*forums_Posts表*/
/*litbbs.dbo.re 移植至Forums.dbo.forums_Posts */
insert into Forums.dbo.forums_Posts
(
ThreadID,
ParentID,
PostAuthor,
UserID,
ForumID,
PostLevel,
SortOrder,
Subject,
PostDate,
IsApproved,
IsLocked,
IsIndexed,
TotalViews,
Body,
FormattedBody,
IPAddress,
PostType
)
select 
id as ThreadID
,id as ParentID
,rename as PostAuthor
,(select k from litbbs.dbo.[user] where name =reName) as UserID
,'34' as ForumID
,'2' as PostLevel
,'2' as SortOrder
,retitle as Subject
,redate as PostDate
,'true' as IsApproved
,'false' as IsLocked
,'false' as IsIndexed
,'1'     as TotalViews
, Body=
case
when CHARINDEX('<',retext)<1 then  SUBSTRING(retext,0,5000)
else SUBSTRING(retext,0,CHARINDEX('<',retext))
end
,FormattedBody=
case
when CHARINDEX('<',retext)<1 then  SUBSTRING(retext,0,5000)
else SUBSTRING(retext,0,CHARINDEX('<',retext))
end
,'' as IPAddress
,'0' as PostType
from litbbs.dbo.re where bbs=5 and id in (select ThreadID from forums_Threads)

/*forums_Posts表*/
/*得到所有列名*/
  select DISTINCT b.name from sysobjects a,syscolumns b,systypes c  
  where a.id=b.id and b.xtype=c.xtype and a.name='forums_Posts'