[原创] SiteServer 3.5 批量导入文章的SQL处理脚本

2005时做过一个小网站，当时是用ASP+Access做的，功能很简单，但里面的文章不少

现在就像把它转移到SS上来，重点就是如何导入文章

本来SS本身提供了批量导入功能，但对于在WEB上一次性导入一万多篇的情况，心理还是不踏实的

另外SS的导入功能没有字段对照，不清楚具体的模板是什么格式的，很怕出些什么问题

 

还是直接采用SQL的方式靠谱一些吧

 

首先，获取要导入站点的基础信息：

--查看站点列表
select * from siteserver_PublishmentSystem

--查看指定站点的栏目列表
select * from siteserver_Node
where publishmentsystemId=<站点ID>

--查看指定站点、指定栏目的文章列表
select * from siteserver_Content
where publishmentsystemId=<站点ID> and NodeId=<栏目ID>

 

然后，编写数据导入语句：

declare @title varchar(500)
declare @body varchar(8000)
declare @date datetime
declare @click int

declare cpos cursor for select contentTitle, contentBody, createDate, clickNum from DB_old.dbo.m04 order by createDate
open cpos
fetch next from cpos into @title,@body,@date,@click
while @@FETCH_STATUS=0
begin
    
    insert into 
    siteserver_content(
        NodeId, PublishmentSystemID, AddUserName, LastEditUserName, LastEditDate
        , Taxis, ContentGroupNameCollection, Tags, SourceID, ReferenceID
        , IsChecked, CheckedLevel, Comments, Hits, HitsByDay, HitsByWeek, HitsByMonth
        , LastHitsDate, SettingsXML
        , Title, SubTitle, ImageUrl, LinkUrl, FileUrl
        , [Content], Summary, Author, Source, IsRecommend, IsHot, IsColor, ConsumePoint
        , IsTop, AddDate
    )
    values(
        <栏目ID>, <站点ID>, 'netwild', 'netwild', @date
        , 1, '', '', 0, 0
        , 'True', 1, 0, @click, 0, 0, 0
        , @date, 'titleformatstring=False_False_False_&consumepoint='
        , @title, '', '', '', ''
        , @body, '', '', '', 'False', 'False', 'False', 0
        , 'False', @date
    )

fetch next from cpos into @title,@body,@date,@click
end
close cpos
deallocate cpos

 

还需要更新一下栏目的文章数量统计：

--更新栏目统计
update node
set node.contentNum=(select count(*) from siteserver_Content as c where c.PublishmentSystemID=node.PublishmentSystemID and c.NodeId=node.NodeId)
from siteserver_Node as node
where node.publishmentsystemId=<站点ID>

 

最后，在SS的管理控制台中，清除缓存就OK啦！