SQL對不同伺服器資料庫之間的資料操作

建立連結伺服器

exec  
 sp_addlinkedserver    
 'ITSV' ,  
 ' ' ,  
 'SQLOLEDB' ,  
 '192.168.0.237'  --sql伺服器ip地址
 
 exec  
 sp_addlinkedsrvlogin   
 'ITSV' 
 ,  
 'false' 
 , 
 null 
 ,  
 'sa' --使用者名稱
 ,  
 '123'  --密碼

select* from ITSV.XYMES_AiQi.AreaInfo

--執行刪除ITSV，不使用時需要刪除否則下次執行會報錯（存在）
 exec  
 sp_dropserver   
 'ITSV' 
 ,  
 'droplogins' 

連線遠端/區域網資料(openrowset/openquery/opendatasource)
1.openrowset
–查詢範例

 select *  from  
 openrowset(  
 'SQLOLEDB ' 
 ,  
 '192.168.0.237' --sql伺服器名ip地址
 ;  
 'sa' --使用者名稱
 ;  
 '123' --密碼
 ,[XYMES_AiQi].[dbo].[AreaInfo])  --資料庫名.dbo.表名

–生成本地表

 select * into  
 AreaInfo  --表
 from  
 openrowset(  
 'SQLOLEDB ' 
 ,  
 '192.168.0.237' --sql伺服器名ip地址
 ;  
 'sa' --使用者名稱
 ;  
 '123' --密碼
 ,[XYMES_AiQi].[dbo].[AreaInfo])  --資料庫名.dbo.表名

–把本地表匯入遠端表

 insert  
 openrowset(  
 'SQLOLEDB ' 
 ,  
 '192.168.0.237' --sql伺服器名ip地址
 ;  
 'sa' --使用者名稱
 ;  
 '123' --密碼
 ,[XYMES_AiQi].[dbo].[AreaInfo])  --資料庫名.dbo.表名
 select * from  [AreaInfo]  --本地表

–更新本地表

 update b  set  b.列A=a.列A  
 from  
 openrowset(  
 'SQLOLEDB ' 
 ,  
 '192.168.0.237' --sql伺服器名ip地址
 ;  
 'sa' --使用者名稱
 ;  
 '123' --密碼
 ,[XYMES_AiQi].[dbo].[AreaInfo])  --資料庫名.dbo.表名
 as  a  inner  join  
 [AreaInfo] b  --本地表
 on  
 a.column1=b.column1  

2.openquery用法需要建立一個連線

–建立一個連線建立連結伺服器

--建立一個連線建立連結伺服器 
 exec  
 sp_addlinkedserver    
 'ITSV ' 
 ,  
 ' ' 
 ,  
 'SQLOLEDB ' 
 ,  
 '192.168.0.237'  --遠端sql伺服器名ip地址

–查詢

--查詢
 select  *  FROM  
 openquery(ITSV,   
 'SELECT *  [XYMES_AiQi].[dbo].[AreaInfo]' --SELECT *  FROM 資料庫.dbo.表名
 )  

–把本地表匯入遠端表

 --把本地表匯入遠端表  
 insert  
 openquery(ITSV,   
 'SELECT *  [XYMES_AiQi].[dbo].[AreaInfo]'  --SELECT *  FROM 資料庫.dbo.表名
 )  
 select  *  from AreaInfo --本地表  

–更新本地表

 --更新本地表  
update  b  set  b.列B=a.列B  FROM  
 openquery(ITSV,   
 'SELECT * [XYMES_AiQi].[dbo].[AreaInfo]'  --SELECT *  FROM 資料庫.dbo.表名
 )  
 as  a   inner  join  
 AreaInfo --本地表 
 b  
 on  
 a.列A=b.列A  
 

3.opendatasource/openrowset

 SELECT  *  FROM    
 opendatasource(  
 'SQLOLEDB ' 
 ,   
 'Data Source=192.168.0.237/ServerName;User ID=sa;Password=123'  --Data Source=ip/ServerName;User ID=登陸名;Password=密碼
 ).[XYMES_AiQi].[dbo].[AreaInfo]  --資料庫.dbo.表名  
 
 
 --把本地表匯入遠端表  
 insert  
 opendatasource(  
 'SQLOLEDB ' 
 ,   
 'Data Source=192.168.0.237/ServerName;User ID=sa;Password=123'  --Data Source=ip/ServerName;User ID=登陸名;Password=密碼
 ).[XYMES_AiQi].[dbo].[AreaInfo]  --資料庫.dbo.表名  
 
 select  *  from [AreaInfo] --本地表

4.EXEC AT使用 必須啟動rpc

 EXEC  
 sp_addlinkedserver  
 'chinancl' 
 , 
 ' ' 
 , 
 'SQLOLEDB' 
 , 
 'CHINANCL-CMS'    
 --新建連線 
 
 EXEC  
 sp_addlinkedsrvlogin  
 'chinancl' 
 , 
 'false' 
 , 
 null 
 , 
 'sa' 
 , 
 'sa'         
 --使用者名稱 密碼 
 
 EXEC  
 sp_serveroption  
 'chinancl' 
 , 
 'rpc out' 
 , 
 true                      
 --啟動RPC 

–刪除連結登入名

 
 EXEC 
 
 ( 
 'create database text ' 
 )  
 at  
 [chinancl] 
 
 EXEC 
 
 ( 
 ' use text 
 
     
 create table text (id int)' 
 )  
 at  
 [chinancl] 

    
 
 exec  
 sp_dropserver  
 'chinancl' 
 , 
 'droplogins'                           
 --刪除連結登入名