场景描述：项目上已经部署好数据库，是局域网内使用，外网无法访问。现场只有普通运维人员，因此当数据库变更时，除非工程师亲自去现场更新数据库，否则必须要提供一个快捷的方式来给运维人员操作。

实现思路：利用一个sql执行文本来实现数据更新，这样运维人员只需打开Sql Server客户端执行sql文本即可。

实现代码：

use DatabaseName
go

--查询连接对象ID
declare @connectobjectid int
select @connectobjectid=ConnectObjectID from DF_ConnectObject where Name='PLC16'

--创建临时表保存待更新点位
create table #table(
variablename varchar(100),
datatype varchar(20),
[length] int,
readandwrite int,
remark varchar(500)
);
insert into #table(variablename,datatype,[length],readandwrite,remark)
select 'AirCon.Sel1_2','BIT',1,1,'0=选择开启一套系统，1=选择开启两套系统'
union select 'AirCon.StartStop','BIT',1,1,'开启/关闭系统'
union select 'PLC2_AI11_AI.PV','FLOAT',4,0,'冷冻水回水支管流量1'
union select 'PLC7_AI07_AI.PV','FLOAT',4,0,'冷冻水回水支管流量2'
union select 'AirCon.CH_DP','FLOAT',4,1,'冷冻水压差设定值'
union select 'AirCon.CW_TS','FLOAT',4,1,'冷却水温差设定值'
union select 'AirCon.COT_T1','FLOAT',4,1,'冷却塔启动温度'
union select 'AirCon.COT_T2','FLOAT',4,1,'冷却塔停止温度'
union select 'PLC16_LP_DYPress01_SP','FLOAT',4,1,'定压补水压力设定值'
union select 'PLC16_LP_DYPressRange01_SP','FLOAT',4,1,'定压补水范围设定值'
union select 'Ari_Staus_Tdb','FLOAT',4,0,'室外温度'
union select 'Ari_Staus_RH','FLOAT',4,0,'室外湿度'
union select 'Ari_Staus_H','FLOAT',4,0,'室外焓值'
union select 'Ari_Staus_AH','FLOAT',4,0,'绝对湿度'
union select 'Ari_Staus_Tdp','FLOAT',4,0,'室外露点温度'
union select 'Ari_Staus_Twb','FLOAT',4,0,'室外湿球温度'
union select 'Ari_KW_CT','FLOAT',4,0,'冷却塔耗电量'
union select 'Ari_KW_CP','FLOAT',4,0,'冷却泵耗电量'
union select 'Ari_KW_FP','FLOAT',4,0,'冷冻泵耗电量'
union select 'Ari_KW_CH','FLOAT',4,0,'制冷机耗电量'
union select 'Ari_KW_SYS','FLOAT',4,0,'制冷系统耗电量'
union select 'Ari_COP_CH','FLOAT',4,0,'制冷机COP'
union select 'Ari_COP_SYS','FLOAT',4,0,'制冷系统COP'	

--利用游标循环临时表中的点位数据进行更新
declare @variablename varchar(100)
declare @datatype varchar(20)
declare @length int
declare @readandwrite int
declare @remark varchar(500)
declare my_cursor cursor --定义游标 
for select * from #table --查出需要的集合放到游标中 
open my_cursor; --打开游标 
fetch next from my_cursor into @variablename,@datatype,@length,@readandwrite,@remark; --读取第一行数据 
while @@fetch_status = 0     
begin
	if exists(select 1 from DF_Variable where VariableName=@variablename)
	begin
		update DF_Variable set ConnectObjectID=@connectobjectid,DataType=@datatype,Length=@length,Address=@variablename,BitOrder=0,
		ReadAndWrite=@readandwrite,IsAlarmVariable=0,Remark=@remark,ModifyDate=getdate(),ModifyUserID='admin'
		where VariableName=@variablename
	end
	else
	begin
		insert into DF_Variable(ConnectObjectID,VariableName,DataType,Length,Address,BitOrder,ReadAndWrite,IsAlarmVariable,Remark,CreateDate,CreateUserID)
		values(@connectobjectid,@variablename,@datatype,@length,@variablename,0,@readandwrite,0,@remark,getdate(),'admin')
	end
　　fetch next from my_cursor into @variablename,@datatype,@length,@readandwrite,@remark; --读取下一行数据     
end 
close my_cursor; --关闭游标 
deallocate my_cursor; --释放游标 go

--最后删除临时表
drop table #table