跨库操作SQL Server数据库的插入、修改

跨库操作SQL Server数据库的插入

create  procedure dbo.p_insertAmdata
as
declare @amid integer
declare @addate datetime
declare @msg varchar(80)
Declare mycursor cursor for select am_id from t_ammeter
open mycursor
fetch next from mycursor into @amid
while @@fetch_status=0
begin
  declare datecursor cursor for select max(ad_date) as maxdate from t_amdata
  open datecursor
  fetch next from datecursor into @addate
  select @msg='----------------------'+Str(@amid)+'---------------------------'
  if @addate<>null
  begin
  print @msg
  print @addate
  insert into t_amdata2 select * from OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=.;User ID=sa;Password=123'
         ).sdcs_data.dbo.t_amdata where am_id=@amid and ad_date>@addate
  end
  else
  begin
  select @msg='----------------------'+str(@amid)+'No date---------------------------'
  print @msg
  insert into t_amdata2 select * from OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=.;User ID=sa;Password=123'
         ).sdcs_data.dbo.t_amdata where am_id=@amid
  end
  close datecursor
  deallocate datecursor

  fetch next from mycursor into @amid
end
close mycursor
deallocate mycursor

 

跨库操作SQL Server数据库的修改

create procedure dbo.p_updatemeter--(
  --@amid integer
--)
as
declare @amid integer
declare @ame1 varchar(64)
Declare mycursor cursor for select am_id from t_ammeter
open mycursor
fetch next from mycursor into @amid
while @@fetch_status=0
begin
  declare amcursor  cursor for select am_e1 from OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=www.slgdgs.cn;User ID=sdcs;Password=sdcs'
         ).sdcs_old.dbo.t_ammeter where am_id=@amid
  open amcursor
  fetch next from amcursor into @ame1
  update t_ammeter set am_e1=@ame1 where am_id=@amid
  close amcursor
  deallocate amcursor
  fetch next from mycursor into @amid
end
close mycursor
deallocate mycursor