update yc
set sl=bd.sl,pjdj=bd.pjdj,kcje=bd.kcje
from dbo.xykc bd,
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=mxp;User ID=sa;Password=abc').mxp_gl.dbo.xykc yc
where (bd.wlpm=yc.wlpm)
and (bd.ckm=yc.ckm)
and (bd.ckm= '一仓库')
and (bd.sl<>yc.sl)
set sl=bd.sl,pjdj=bd.pjdj,kcje=bd.kcje
from dbo.xykc bd,
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=mxp;User ID=sa;Password=abc').mxp_gl.dbo.xykc yc
where (bd.wlpm=yc.wlpm)
and (bd.ckm=yc.ckm)
and (bd.ckm= '一仓库')
and (bd.sl<>yc.sl)
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','mxp'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','abc'
go--更新
update yc
set sl=bd.sl,pjdj=bd.pjdj,kcje=bd.kcje
from dbo.xykc bd,srv_lnk.mxp_gl.dbo.xykc yc
where (bd.wlpm=yc.wlpm)
and (bd.ckm=yc.ckm)
and (bd.ckm= '一仓库')
and (bd.sl<>yc.sl)
go--不用时删除链接服务器:
exec sp_dropserver 'srv_lnk','droplogins'
谢谢你!我照你的去试了下,提示错误:
"服务器:消息208,级别16,状态1,行11
对象名 'yc'无效。"希望得到你进一步帮助,谢谢!
“
--更新
update yc -- 这句应改为:srv_lnk.mxp_gl.dbo.xykc才行
set sl=bd.sl,pjdj=bd.pjdj,kcje=bd.kcje
from dbo.xykc bd,srv_lnk.mxp_gl.dbo.xykc yc
where (bd.wlpm=yc.wlpm)
and (bd.ckm=yc.ckm)
and (bd.ckm= '一仓库')
and (bd.sl<>yc.sl)”
运行的结果跟我第一楼代码运得的结果一样的。要把"update yc "改为:"srv_lnk.mxp_gl.dbo.xykc"就可以了;不知为何,以前试“接服务器”sp_addlinkedserver 来操作时总会比OPENDATASOURCE速度慢些。
另附:
在第一楼中运行结果没有任何错误提示,只是“所影响的行数“好像过多了。执行完语句后,运行:
”
select yc.*
FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=mxp;
User ID=sa;Password=abc').mxp_gl.dbo.xykc yc , xykc bd
where (bd.wlpm=yc.wlpm)
and(bd.ckm=yc.ckm)
and(bd.ckm= '一仓库')
and(bd.sl<>yc.sl)
“不知为何还有132条记录显示,且这些记录的sl,pjdj,kcje字段值都一样(当然是错误值)
set sl=bd.sl,pjdj=bd.pjdj,kcje=bd.kcje
from dbo.xykc bd,srv_lnk.mxp_gl.dbo.xykc yc
where (bd.wlpm=yc.wlpm)
and (bd.ckm=yc.ckm)
and (bd.ckm= '一仓库')
and (bd.sl<>yc.sl)
if object_id('test') is not null
drop table test
create table test(id int,txt varchar(10))
insert test select 1,'aa'
union all select 2,'ab'
go--本地创建测试表
if object_id('test') is not null
drop table test
create table test(id int,txt varchar(10))
insert test select 1,'a1' --这条记录的值与远程不同
union all select 2,'ab'
go--创建链接服务器
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa',''
go--更新远程服务器
update b
set txt=a.txt
from test a,srv_lnk.pubs.dbo.test b
where a.id=b.id and a.txt<>b.txt--显示更新结果
select * from test
select * from srv_lnk.pubs.dbo.test
go--删除链接服务器
exec sp_dropserver 'srv_lnk',droplogins/*--测试结果id txt
----------- ----------
1 a1
2 ab(所影响的行数为 2 行)id txt
----------- ----------
1 a1
2 ab(所影响的行数为 2 行)--*/
------ 可能是我开始搞错了(在SQL查询分析器中选错了窗口/执行语句);
又试了几次:“update yc”跟“srv_lnk.mxp_gl.dbo.xykc”执行结果是同等的