DB研討會 : [轉貼]Microsoft SQL Server 跨資料庫查詢

USE [NowDatabase]
--創建服務器鏈接
EXEC sp_addlinkedserver  'other_srv_lnk','','SQLOLEDB','IP,Port'
EXEC sp_addlinkedsrvlogin 'other_srv_lnk','false',null,'Account','AccountPwd'
GO
--插入資料表使用查訊語句（從另一台服務器把資料塞過來本地資料表）
INSERT INTO [NowDatabase].dbo.[New_UpdateAML]
           ([PN]
           ,[VC]
           ,[uAML]
           ,[Comment]
           ,[plant]
           ,[mfr]
           ,[mfpn]
           ,[isPAS]
           ,[InsertUser]
           ,[InsertDateTime]
           ,[isEnable]
           ,[Remark])
SELECT	UpdateAML.PN , UpdateAML.VC , UpdateAML.UAML , UpdateAML.Comment , UpdateAML.Plant , UpdateAML.mfr , UpdateAML.mfrpn , 0 , 'UserName' , getDate() , 1 , 'Insert From Server1'
FROM	other_srv_lnk.[DatabaseName].[dbo].[UpdateAML]
WHERE 	UpdateAML.PN IS NOT NULL
AND 	UpdateAML.VC IS NOT NULL
AND 	UpdateAML.UAML IS NOT NULL
AND		UpdateAML.Plant IS NOT NULL
GO
--不再使用時刪除鏈接服務器
EXEC sp_dropserver 'other_srv_lnk','droplogins'
GO