use P2 goif object_id('#tmp') is not null drop table #tmpselect id, M into #tmp from opendatasource('sqloledb', 'data source = .; uid = sa; pwd = sa').P1.dbo.Aupdate B set N = #tmp.M from B, #tmp where B.id = #tmp.iddrop table #tmp
--创建测试环境 create database p1 create database p2 create table p1.dbo.A(id int,m varchar) create table p2.dbo.B(id int,n varchar)--执行更新 update p2.dbo.b set n=m from p1.dbo.a where p2.dbo.b.id=p1.dbo.a.id--删除测试环境 drop database p1 drop database p2
--执行更新 update p2.dbo.b set n=m from p1.dbo.a where p2.dbo.b.id=p1.dbo.a.id 只from p1.dbo.a,肯定不行吧?
update P2.dbo.B set N=x.M from P1.dbo.A x inner join P2.dbo.B c on c.id = x.id
SET IDENTITY_INSERT on insert p2..b(id,n) select id ,m from a SET IDENTITY_INSERT off
先确定2个数据库之间有建立映射关系 在执行 lt1129(修理地球)的 update P2.dbo.B set N=x.M from P1.dbo.A x inner join P2.dbo.B c on c.id = x.id
goif object_id('#tmp') is not null
drop table #tmpselect id, M into #tmp from
opendatasource('sqloledb', 'data source = .; uid = sa; pwd = sa').P1.dbo.Aupdate B set N = #tmp.M
from B, #tmp
where B.id = #tmp.iddrop table #tmp
create database p1
create database p2
create table p1.dbo.A(id int,m varchar)
create table p2.dbo.B(id int,n varchar)--执行更新
update p2.dbo.b set n=m from p1.dbo.a where p2.dbo.b.id=p1.dbo.a.id--删除测试环境
drop database p1
drop database p2
update p2.dbo.b set n=m from p1.dbo.a where p2.dbo.b.id=p1.dbo.a.id
只from p1.dbo.a,肯定不行吧?
set N=x.M
from P1.dbo.A x
inner join P2.dbo.B c
on c.id = x.id
insert p2..b(id,n) select id ,m from a
SET IDENTITY_INSERT off
在执行
lt1129(修理地球)的
update P2.dbo.B
set N=x.M
from P1.dbo.A x
inner join P2.dbo.B c
on c.id = x.id