SQL 循环插入语句和分表以及连接局域内的另一台sql的语句

来源：互联网发布：第一个网络实名制国家编辑：程序博客网时间：2024/05/15 20:43

CREATE INDEX CurrentData_index ON dbo.t_CurrentData(DeviceName,state) 创建索引。

declare @sDate varchar(20)
declare @eDate varchar(20)
declare @mindate datetime
declare @maxdate datetime
declare @mindateStr varchar(20)
declare @maxdateStr varchar(20)
declare @value float

set @sDate = '2015-1-30 00:00:00'
set @eDate = '2015-2-10 00:00:00'

set @mindate = cast(@sDate as datetime)
set @maxdate = dateadd(day,1,@mindate)

while @maxdate <= cast(@eDate as datetime)
begin
  set @mindateStr = convert(varchar(19),@mindate,120)
  set @maxdateStr = convert(varchar(19),@maxdate,120)
  --print(@mindateStr)
  print(@maxdateStr)
  --插数
  --insert into
select @value = avg(DataValue)from dbo.HistoryData where Collectiontime between @mindate and @maxdate and Code = 'code' group by Code
  insert into HistoryData values(@value,'code',@maxdate,name,1,0,0,'hour1','N','','模拟量','N','N','N','N',0,'N','N','--','--','--','','','')
  set @mindate = @maxdate
  set @maxdate = dateadd(day,1,@maxdate)
end

--两张表结构一样，且都是创建好的
insert into t_HistoryDataOriginal_2013 select * from t_HistoryDataOriginal where [DataTime] between '2011-01-01' and '2013-12-31';

--另一张表没有创建的
select * into t_HistoryDataOriginal_2013 from t_HistoryDataOriginal where [DataTime] between '2011-01-01' and '2013-12-31';

exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','192.168.45.34'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','Zxjc321'
go

--查询示例
--select * from dbo.t_HistoryDataOriginal where IsUpload = 'Y' order by dataTime
--select top 100* from srv_lnk.monitorsystem.dbo.t_HistoryDataOriginal order by DataTime desc

--导入示例

insert into srv_lnk.monitorsystem.dbo.t_HistoryDataOriginal select * from monitorsystem.dbo.t_HistoryDataOriginal where IsUpload = 'N'
update monitorsystem.dbo.t_HistoryDataOriginal set IsUpload='Y'where IsUpload='N'
--select * into 表 from srv_lnk.数据库名.dbo.t_HistoryDataOriginal

go
--处理完成后删除链接服务器
exec sp_dropserver 'srv_lnk','droplogins'

0 0