跨数据库CRUD操作

• 查询、检索(Retrieve)

select * from [A数据库].[dbo].[A表] a ,
OPENDATASOURCE( 'SQLOLEDB','Data Source=192.168.0.1;User ID=sa;Password=123456').[B数据库].[dbo].[A表] b
where  a.id=b.id (条件)

• 修改、更新(Update)

update b set b.data =a.data
from [A数据库].[dbo].[A表] a ,
OPENDATASOURCE( 'SQLOLEDB','Data Source=192.168.0.1;User ID=sa;Password=123456').[B数据库].[dbo].[A表] b
where a.id=b.id (条件)

• 增加(Create)

insert into [A数据库].[dbo].[A表] 
select *from OPENDATASOURCE( 'SQLOLEDB','Data Source=192.168.0.1;User ID=sa;Password=123456').[B数据库].[dbo].[A表] 
where a.id=b.id (条件)

• 删除(Delete)

delete from OPENDATASOURCE( 'SQLOLEDB','Data Source=192.168.0.1;User ID=sa;Password=123456').[B数据库].[dbo].[A表] 
where a.id=b.id (条件)