34,591
社区成员
发帖
与我相关
我的任务
分享
if exists(select 1 from sys.servers where name='CServer')
begin
Exec sp_droplinkedsrvlogin CServer,Null --删除隐射
Exec sp_dropserver CServer --删除服务器
end
EXEC sp_addlinkedserver --创建DBLINK
@server= 'CServer',--被訪問的服務器別名
@srvproduct= '',
@provider= 'SQLOLEDB',
@datasrc= '113.110.94.210,1433' --要訪問的服務器
EXEC sp_addlinkedsrvlogin
'CServer', --被訪問的服務器別名
'false',
NULL,
'sa', --帳號
'12345' --密碼
insert into Test.dbo.Detect
select a.*
from CServer.test1.dbo.Detect1 a
left join Test.dbo.Detect b on a.[主键]=b.[主键]
where b.[主键] is null
--a 本地表 tablename 远程表 此处令其别名为b,若两表列若不同,将列列出即可,
--关联条件a的主键pk1,pk2和远程服务器表(此处别名b)的主键,有几个关联几个
insert into a
select * from [server].[database].[dbo].[tablename] b
where not exists (select 1
from a a1
where a1.pk1=b.pk1 and a1.pk2=b.pk2);
IF EXISTS (SELECT * FROM sys.linked_logins WHERE
server_id IN (SELECT server_id FROM sys.servers where name = 'CServer')
AND local_principal_id = (
select principal_id from sys.server_principals
where name = 'sa' and type in ('S', 'U')
))
Exec sp_droplinkedsrvlogin CServer,Null
go
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CServer')
EXEC master.dbo.sp_dropserver @server=N'CServer'
go