前提概要:
表:员工表O_StaffInfo,员工卡片表Table_1
需求:根据员工姓名将【卡片表Table_1】的卡号更新到【员工表O_StaffInfo】卡号字段
declare @sql varchar(300)
declare @tablecolumnname varchar(100) --存放员工姓名
declare cursor1 cursor for --游标
select StaffName from [dbo].[O_StaffInfo] where Isdel=0
open cursor1 --将员工信息存储到游标
fetch next from cursor1 into @tablecolumnname
while @@fetch_status=0
begin
--print @tablecolumnname
declare @code varchar(100)
set @code=(select code from Table_1 where name=@tablecolumnname) --根据员工姓名查询出卡号
set @sql='update O_StaffInfo set CareNo='''+@code+''' where StaffName='''+@tablecolumnname+'''' --拼接修改的语句
print @sql --打印
exec(@sql) --执行
fetch next from cursor1 into @tablecolumnname
end
close cursor1
deallocate cursor1