|
自己写的游标操作语句
北大国家发展研究院
论坛原来采用老式论坛，为接入Discuz论坛，需要对原来的数据进行处理，
经过一周的数据处理，终于远程升级。
部分升级脚本（迁移贴子）如下：
declare my_cursor cursor scroll
for www.2cto.com
SELECT [ID]
,[TITLE]
,[CONTENT]
,[AUTHOR_ID]
,[AUTHOR_NAME]
,[AUTHOR_TEXT]
,[AUTHOR_SIG]
,[REPLY_TO]
,[POST_TIME]
,[BELONG_TO]
,[LOCKED]
,[POLL_COUNT]ccc
,[CLICK_COUNT]
,[REPLY_COUNT]
,[LAST_UPDATE]
,[DELETED]
,[IP]
,[LINKURL]
,[LINKIMG]
,[Flinkname]
,[Flinkurl]
FROM [ccerforum].[dbo].[ARTICLES] where id>0 ORDER BY ID
open my_cursor
declare @ID int,@TITLE nvarchar(100)
,@CONTENT nvarchar(3000)
,@AUTHOR_ID int www.2cto.com
,@AUTHOR_NAME nvarchar(40)
,@AUTHOR_TEXT nvarchar(120)
,@AUTHOR_SIG nvarchar(100)
,@REPLY_TO int
,@POST_TIME smalldatetime
,@BELONG_TO int
,@LOCKED smallint
,@POLL_COUNT int
,@CLICK_COUNT int
,@REPLY_COUNT int
,@LAST_UPDATE smalldatetime
,@DELETED smallint
,@IP nchar(15)
,@LINKURL nvarchar(128)
,@LINKIMG nvarchar(128)
,@Flinkname nvarchar(64)
,@Flinkurl nvarchar(64)
declare @NewTopicId int,@NewLayer int,@AttachmentNum int,@lastpostid int
fetch next from my_cursor into @ID ,@TITLE,@CONTENT,@AUTHOR_ID,
@AUTHOR_NAME,@AUTHOR_TEXT,@AUTHOR_SIG,@REPLY_TO,@POST_TIME,@BELONG_TO,
@LOCKED,@POLL_COUNT,@CLICK_COUNT,@REPLY_COUNT,@LAST_UPDATE,@DELETED,@IP,
@LINKURL,@LINKIMG,@Flinkname,@Flinkurl
while(@@fetch_status=0)
begin
if @REPLY_TO is null or @REPLY_TO=''
set @NewLayer=0
else
set @NewLayer=1
if @Flinkurl is null or @Flinkurl=''
set @AttachmentNum=0
else
set @AttachmentNum=1
if @AUTHOR_ID is null or @AUTHOR_ID=''
set @AUTHOR_ID=isnull((SELECT [ID] FROM [ccerforum].[dbo].[USERS] where [NAME]=@AUTHOR_TEXT ),0)
if @REPLY_TO is null or @REPLY_TO=''
begin www.2cto.com
print 'replayto= null'
set @lastpostid=(select(max(pid)+1) from [nsd_forum_discuz25].[dbo].[dnt_posts1] )
INSERT INTO [nsd_forum_discuz25].[dbo].[dnt_topics]
([fid]
,[iconid]
,[typeid]
,[readperm]
,[price]
,[poster]
,[posterid]
,[title]
,[postdatetime]
,[lastpost]
,[lastpostid]
,[lastposter]
,[lastposterid]
,[views]
,[replies]
,[displayorder]
,[highlight]
,[digest]
,[rate]
,[hide]
,[poll]
,[attachment]
,[moderated]
,[closed]
,[magic]
,[identify]
,[special])
VALUES
(@BELONG_TO
,0
,0
,0
,0
,@AUTHOR_NAME
,@AUTHOR_ID
,@TITLE
,@POST_TIME
,@POST_TIME
,@AUTHOR_ID
,@AUTHOR_NAME
,@AUTHOR_ID
,100
,@REPLY_COUNT
,0
,''
,0
,0
,0
,0
,@At |