USERID |
1,2,3,4 |
4,5,6,7 |
下面这张表格是一张影射表,从userData表中找到与map_table相匹配的old_ID,并且用new_ID来更新响应的USERID
map_table
一开始考虑的时候是先把数据取出来,在CS代码中处理完以后再存放进去。但是这里遇到一个问题就是要一行一行地遍历userData表格,这就使得代码相当地难处理。既然是行处理,于是想到了用游标来解决这个问题。花了将近6个小时,终于把这个问题解决了,学到了不少东西,呵呵,贴上来分享。更新以后的userData表格如下:
USERID |
Y111,Y222,Y333,Y444 |
Y555,Y666,Y777,Y888 |
-- Updating the Rows through CURSOR --
DECLARE @userid VARCHAR( 200 ) , @new_ID VARCHAR( 200 )
-- 逗号的索引位置 --
DECLARE @index int
-- 逗号 --
DECLARE @comma varchar( 3 )
-- 存放sec_usrdata表里的userid --
DECLARE @temp VARCHAR( 200 )
-- 保存要更新的数据 --
DECLARE @sum varchar( 200 )
DECLARE @new_ID_backup varchar( 200 )
-- 查询纪录的行数 --
DECLARE @rowCount int
-- 初始化 --
set @comma = ' , '
set @index = 0
set @sum = ''
set @temp = ''
set @new_ID_backup = ''
set @rowCount =- 1
-- 声明游标 --
DECLARE curr1 CURSOR
FOR SELECT USERID from SEC_USRDATA for Update
OPEN curr1
FETCH next from curr1 into @userid
WHILE (@@FETCH_status = 0 )
BEGIN
-- 清空内容 --
set @sum = ''
set @rowCount =- 1
set @index = CHARINDEX(@comma,@userid, 0 )
set @temp = SUBSTRING(@userid, 0 ,@index)
Select @new_ID = new_ID from Map_Table where old_id = @temp
select @rowCount = count( * ) from Map_Table where old_id = @temp
if (@rowCount = 0 )
begin
set @sum = @sum + @temp + @comma
end
if (@rowCount > 0 )
begin
set @sum = @sum + @new_ID + @comma
end
while (@index < datalength(@userid))
begin
set @userid = SUBSTRING(@userid,@index + 1 ,datalength(@userid))
set @index = CHARINDEX(@comma,@userid, 0 )
if (@index = 0 )
begin
set @temp = SUBSTRING(@userid, 0 ,datalength(@userid) + 1 )
Select @new_ID = new_ID from Map_Table where old_id = @temp
select @rowCount = count( * ) from Map_Table where old_id = @temp
if (@rowCount = 0 )
begin
set @sum = @sum + @temp
print @sum
end
if (@rowCount > 0 )
begin
set @sum = @sum + @new_ID
print @sum
end
break
end
else
begin
set @temp = SUBSTRING(@userid, 0 ,@index)
Select @new_ID = new_ID from Map_Table where old_id = @temp
select @rowCount = count( * ) from Map_Table where old_id = @temp
if (@rowCount = 0 )
begin
set @sum = @sum + @temp + @comma
print @sum
end
if (@rowCount > 0 )
begin
set @sum = @sum + @new_ID + @comma
print @sum
end
end
end
if (@rowCount >= 0 )
begin
update SEC_USRDATA Set USERID = @sum Where current of curr1
FETCH NEXT FROM curr1 INTO @userid
end
else
begin
Raiserror( ' Update Date failure ' , 10 , 1 )
break
end
END
-- CLOSE the CURSOR --
CLOSE curr1
-- Deallocate the CURSOR --
DEALLOCATE curr1