跨库操作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