服务器端游标快要把我折磨死了，我要将[temp_user]中的记录合并到[WAPCOUNT_USER]中，遍历表[temp_user]判断在[WAPCOUNT_USER]中是否有相同的记录，有相同的记录则更新相关的记录，没有相同的记录则插入这条记录。
我彻底明白了为什么所有人都在说不要用服务器游标了，35W条记录这个存储过程执行了3个小时，我晕死，哪个高手帮忙解决一下撒。能不能用别的方法替代服务器端游标来实现遍历啊？
CREATE proc dbo.user_sql as
declare user_cursor cursor for
SELECT WAPTD_USERID,ACCESS_NUM,CREATETIME from[BackUpTableNew].[xc_count].[temp_user]
open user_cursor
declare @user_cursor_USERID varchar(100)
declare @user_cursor_ACCESS_NUM int
declare @user_cursor_CREATETIME datetime
fetch next from user_cursor into @user_cursor_USERID,@user_cursor_ACCESS_NUM,@user_cursor_CREATETIME
while @@FETCH_STATUS = 0
begin
declare @WAPTD_VIEW_WAPTD_LOGIN_USERNAME varchar(50)
declare @WAPTD_VIEW_PHONENUMBER varchar(20)
declare @WAPTD_VIEW_MOBILETYPE varchar(100)
declare @WAPTD_VIEW_REFER varchar(1000)
declare @WAPTD_VIEW_REMOTE_ADDR varchar(20)
select @WAPTD_VIEW_WAPTD_LOGIN_USERNAME=WAPTD_LOGIN_USERNAME,@WAPTD_VIEW_PHONENUMBER=PHONENUMBER, @WAPTD_VIEW_MOBILETYPE=MOBILETYPE,@WAPTD_VIEW_REFER=REFER, @WAPTD_VIEW_REMOTE_ADDR=REMOTE_ADDR from [BackUpTableNew].[dbo].[wapcount] where createtime = @user_cursor_CREATETIME and waptd_userid=@user_cursor_USERID
if EXISTS (Select ACCESS_NUM,DIFF_DAY_ACCESSFrom[BackUpTableNew].[dbo].[WAPCOUNT_USER] Where WAPTD_USERID=@user_cursor_USERID)
begin
UPDATE[BackUpTableNew].[dbo].[WAPCOUNT_USER] SET ACCESS_NUM=ACCESS_NUM+@user_cursor_ACCESS_NUM,DIFF_DAY_ACCESS=DIFF_DAY_ACCESS+1Where WAPTD_USERID=@user_cursor_USERID
end
else
begin
Insert Into[BackUpTableNew].[dbo].[WAPCOUNT_USER] (WAPTD_USERID,WAPTD_LOGIN_USERNAME,PHONENUMBER,MOBILETYPE,CREATETIME,ACCESS_NUM,REMOTE_ADDR,FIRST_URL,LASTTIME,UP_ACCESS_DATE,DIFF_DAY_ACCESS) VALUES (@user_cursor_USERID,@WAPTD_VIEW_WAPTD_LOGIN_USERNAME,@WAPTD_VIEW_PHONENUMBER,@WAPTD_VIEW_MOBILETYPE,@user_cursor_CREATETIME,@user_cursor_ACCESS_NUM,@WAPTD_VIEW_REMOTE_ADDR,@WAPTD_VIEW_REFER,@user_cursor_CREATETIME,@user_cursor_CREATETIME,1)
end
fetch next from user_cursor into @user_cursor_USERID,@user_cursor_ACCESS_NUM,@user_cursor_CREATETIME
end
close user_cursor
deallocate user_cursor
delete [BackUpTableNew].[xc_count].[temp_user]
GO
|
