if exists(select * from dbo.sysobjects
where id=object_id('contentCopyChannerId') and objectproperty(id,'isprocedure')=1)
drop procedure contentCopyChannerId
go
create proc contentCopyChannerId as
declare @ID numeric
declare @DETAILID numeric
declare @USERID numeric
declare @BASECHANNEL numeric
declare @NEWBASECHANNEL numeric
declare @TITLE varchar(300)
declare @CREATETIME datetime
declare @SOURCE varchar(100)
declare @SOURCEURL varchar(100)
declare @FINGER varchar(100)
declare @AUTH_TYPE numeric
declare @ATTACHMENT_COUNT numeric
declare @COMMENT_COUNT numeric
declare @HITS numeric
declare @displaytime datetime
declare @isdel numeric
declare @titlePicture numeric
declare @style varchar(20)
declare @titlecolor varchar(50)
declare @TOP char(1)
declare @TOPTIME datetime
declare @channel3g_Click int
declare @isCopy varchar(50)
declare @State int
declare @newID numeric
declare @CONTENT_CHANNEL_STATUS numeric
declare @CONTENT_CHANNEL_CREATETIME datetime
declare @CONTENT_CHANNEL_PUBLISHTIME datetime
Set @State = 0
declare @contentCursor cursor --内容
declare @contentChannelCursor cursor --内容频道关系
Begin Tran
set @contentCursor=cursor for
select top 2 [ID]
, DETAILID
, USERID
,( case BASECHANNEL
when '100339' then '101133'
when '100340' then '101134'
when '100343' then '101117'
when '100317' then '100985'
when '100319' then '100985'
when '100661' then '100984'
when '100311' then '100986'
when '100316' then '100981'
when '101111' then '101004'
when '100315' then '100982'
when '101110' then '101006'
when '100318' then '100990'
when '100342' then '101123'
when '100312' then '101120'
when '100627' then '101122'
end) as NEWBASECHANNEL
, BASECHANNEL
, TITLE
, CREATETIME
, SOURCE
, SOURCEURL
, FINGER
, AUTH_TYPE
, ATTACHMENT_COUNT
, COMMENT_COUNT
, HITS
, displaytime
, isdel
, titlePicture
, style
, titlecolor
, [TOP]
, TOPTIME
, channel3g_Click
from CONTENT
where BASECHANNEL in( --100339
--,100340
--,100343
--,100317
--,100319
--,100661
100311
--,100316
--,101111
--,100315
--,101110
--,100318
--,100342
--,100312
--,100627
) and (isCopy is null or isCopy='')
open @contentCursor
--如果没有任何行则直接退出
If @@Cursor_Rows = 0
Begin
Close @contentCursor
Deallocate @contentCursor
End
fetch next from @contentCursor into
@ID
, @DETAILID
, @USERID
, @NEWBASECHANNEL
, @BASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
while(@@fetch_status=0)
begin
set @isCopy=1
insert into CONTENT(
DETAILID
, USERID
, BASECHANNEL
, TITLE
, CREATETIME
, SOURCE
, SOURCEURL
, FINGER
, AUTH_TYPE
, ATTACHMENT_COUNT
, COMMENT_COUNT
, HITS
, displaytime
, isdel
, titlePicture
, style
, titlecolor
, [TOP]
, TOPTIME
, channel3g_Click
, isCopy
)
values(
@DETAILID
, @USERID
, @NEWBASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
, @isCopy
)
Set @newID = @@Identity
update CONTENT set isCopy='2'
where (isCopy is null or isCopy='') and ID=@ID
set @contentChannelCursor=cursor for --内容频道关系结果集
select CREATETIME, PUBLISHTIME, STATUS
from CONTENT_CHANNEL
where CHANNEL_ID=@BASECHANNEL
and CONTENT_ID=@ID
Open @contentChannelCursor
--如果没有任何行则直接退出
If @@Cursor_Rows = 0
Begin
Close @contentChannelCursor
Deallocate @contentChannelCursor
End
fetch next from @contentChannelCursor into @CONTENT_CHANNEL_CREATETIME, @CONTENT_CHANNEL_PUBLISHTIME, @CONTENT_CHANNEL_STATUS
Close @contentChannelCursor
Deallocate @contentChannelCursor
insert into CONTENT_CHANNEL(
CONTENT_ID
, CHANNEL_ID
, CREATETIME
, PUBLISHTIME
, STATUS
, isCopy
)values
(
@newID
,@NEWBASECHANNEL
,@CONTENT_CHANNEL_CREATETIME
,@CONTENT_CHANNEL_PUBLISHTIME
,@CONTENT_CHANNEL_STATUS
,'1'
)
update CONTENT_CHANNEL set isCopy='2'
where (isCopy is null or isCopy='')
and CHANNEL_ID=@BASECHANNEL
and CONTENT_ID=@ID
If @@Error <> 0
Begin
Rollback Tran
Close contentCursor
Deallocate contentCursor
Return -1
End
fetch next from @contentCursor into
@ID
, @DETAILID
, @USERID
, @NEWBASECHANNEL
, @BASECHANNEL
, @TITLE
, @CREATETIME
, @SOURCE
, @SOURCEURL
, @FINGER
, @AUTH_TYPE
, @ATTACHMENT_COUNT
, @COMMENT_COUNT
, @HITS
, @displaytime
, @isdel
, @titlePicture
, @style
, @titlecolor
, @TOP
, @TOPTIME
, @channel3g_Click
End
Close @contentCursor
Deallocate @contentCursor
Commit Tran
EXECUTE contentCopyChannerId
分享到:
相关推荐
在Windows下通过MySql提供的主从复制技术实现数据库主从同步机制,文档中详细记录我在项目中配置的实践过程
读写分离时主从复制的数据延迟如何处理,方便你快速上手,及学习。
mysql读写分离、主从同步
MySQL主从复制与读写分离MySQL主从复制与读写分离
基于Mycat的Mysql主从复制读写分离配置详解与示例,DBA必看的
详细描述MySQL主从同步配置过程
spring4.0.2+mybatis3.2.4+sharding-jdbc1.4.2实现分库分表、主从及事物支持
MySQL主从复制是一个异步的复制过程,底层是基于MySQL数据库自带的二进制日志功能。提前准备好两台服务器,分别安装MySQL并启动服务成功。
在实际开发中,经常会使用到主从的显示,当选中一个父项,则子项的集合自动进行修改, 我们可以使用传统的SelectedIndexChanged修改去实现,但是通常情况下借助于collectionViewSource类更方便的来实现。 一共创建了...
MySQL主从同步与读写分离配置图文详解
mysql主从备份和dz读写分离
mysql主从搭建过程
mysql 多主从一 windows
MySQL的主从配置,读写分离,详细完整教程,pdf文档思维导图
经本人测试已经成功实现
要建立两个主从表,main和detail主从表,在TDataSetProvider中beforeupdaterecord中控制。 服务端建立主从关系,客户端可以共用一个主从关系即可。
mysql主从同步配置详解、一台服务器两个mysql,本地安装两个mysql实现主从同步
MYCAT主从复制+读写分离。。。。。。。。。。。。。。。。
MySQL主从复制与读写分离