SQL不同伺服器資料庫之間的資料操作整理(完整版)

標籤：

本文原網址：http://blog.csdn.net/htl258/article/details/5695391

------------------------------------------------------------------------------------ Author : htl258(Tony)-- Date   : 2010-06-25 22:23:18-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)--          Jul  9 2008 14:43:34--          Copyright (c) 1988-2008 Microsoft Corporation--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)-- Blog   : http://blog.csdn.net/htl258（轉載保留此資訊）-- Subject: SQL不同伺服器資料庫之間資料操作整理------------------------------------------------------------------------------------1. 建立連結的伺服器--1.1 建立一個連結名EXEC sp_addlinkedserver ‘LinkName‘,‘‘,‘SQLOLEDB‘,‘遠程伺服器名或ip地址‘ --有自訂執行個體名還要加上"/執行個體名"    /*例如：EXEC sp_addlinkedserver ‘TonyLink‘,‘‘,‘SQLOLEDB‘,‘192.168.58.208‘ */  --1.2 建立登入資訊(或叫建立連結的伺服器登入名稱映射)(只需選擇一種方式)--1.2.1 以windows認證的方式登入EXEC sp_addlinkedsrvlogin ‘LinkName‘  --或EXEC sp_addlinkedsrvlogin ‘LinkName‘,‘true‘    /*例如：EXEC sp_addlinkedsrvlogin ‘TonyLink‘  */  --1.2.2 以SQL認證的方式登入                                                                    EXEC sp_addlinkedsrvlogin ‘LinkName‘,‘false‘,NULL,‘使用者名稱‘,‘密碼‘    /*例如：EXEC sp_addlinkedsrvlogin ‘TonyLink‘,‘false‘,null,‘sa‘,‘123‘ */ --2. 連結的伺服器相關資料操作--2.1 查詢樣本SELECT * FROM LinkName.資料庫名.架構名.表名    /*例如：SELECT * FROM TonyLink.Mydb.dbo.tb */--2.2 匯入樣本SELECT * INTO 表名 FROM LinkName.資料庫名.架構名.表名    /*例如：SELECT * INTO Newtb FROM TonyLink.Mydb.dbo.tb */--2.3 更新樣本UPDATE LinkName.資料庫名.架構名.表名 SET 欄位=‘值‘ WHERE 欄位=‘條件‘    /*例如：UPDATE TonyLink.Mydb.dbo.tb SET Persons=‘g‘ WHERE Persons=‘a‘ */--2.4 刪除樣本DELETE LinkName.資料庫名.架構名.表名 WHERE 欄位名=‘條件‘    /*例如：DELETE TonyLink.Mydb.dbo.tb WHERE Persons=‘g‘ */ --3. 通過行集合函式(OPENQUERY/OPENROWSET/OPENDATASOURCE)操作方法--3.1 OPENQUERY 方法（需要藉助剛建立的連結的伺服器）：--3.1.1 查詢樣本SELECT * FROM OPENQUERY(LinkName,‘SELECT * FROM 資料庫名.架構名.表名‘)    /* 例如：SELECT * FROM OPENQUERY(TonyLink,‘SELECT * FROM Mydb.dbo.tb‘)  */--3.1.2 匯入樣本--3.1.2.1 匯入所有列INSERT OPENQUERY(LinkName, ‘SELECT *  FROM 資料庫名.架構名.表名‘) SELECT * FROM 本地表    /* 例如：INSERT OPENQUERY(TonyLink,‘SELECT *  FROM Mydb.dbo.tb‘) SELECT * FROM tb */--3.1.2.2 匯入指定列INSERT OPENQUERY(LinkName, ‘SELECT *  FROM 資料庫名.架構名.表名‘) (列,列...)    SELECT 列,列... FROM 本地表    /* 例如：INSERT OPENQUERY(TonyLink,‘SELECT *  FROM Mydb.dbo.tb‘)(RANGE,LEVEL,Persons)           SELECT RANGE,LEVEL,Persons FROM tb    */--3.1.3 更新樣本UPDATE OPENQUERY(LinkName, ‘SELECT * FROM 資料庫名.架構名.表名‘)  SET 欄位=‘值‘ WHERE 欄位=‘條件‘    /*例如：UPDATE OPENQUERY(TonyLink, ‘SELECT * FROM Mydb.dbo.tb‘)  SET Persons=‘g‘ WHERE Persons=‘a‘ */--3.1.4 刪除樣本DELETE OPENQUERY(LinkName, ‘SELECT * FROM 資料庫名.架構名.表名‘)  WHERE 欄位名=‘條件‘    /*例如：DELETE OPENQUERY(TonyLink, ‘SELECT * FROM Mydb.dbo.tb‘)  WHERE Persons=‘g‘ */ --3.2 OPENROWSET方法(不需要用到建立好的連結名。如果串連的執行個體名不是預設的，需要在"sql伺服器名或IP地址"後加上"/執行個體名")--3.2.1 查詢樣本--3.2.1.1 Windows認證方式查詢(以下方法之一即可)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘,‘SELECT * FROM 資料庫名.架構名.表名‘)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘,‘SELECT * FROM 資料庫名.架構名.表名‘)    /* 例如：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘Server=192.168.58.208;Trusted_Connection=yes‘,Mydb.dbo.tb)         或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘Server=192.168.58.208;Trusted_Connection=yes‘,Mydb.dbo.tb)    或：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘Server=192.168.58.208;Trusted_Connection=yes‘,‘SELECT * FROM Mydb.dbo.tb‘)     或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘Server=192.168.58.208;Trusted_Connection=yes‘,‘SELECT * FROM Mydb.dbo.tb‘)    */--3.2.1.2 SQL認證方式查詢(以下方法之一即可)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,‘SELECT * FROM 資料庫名.架構名.表名‘)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,‘SELECT * FROM 資料庫名.架構名.表名‘)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘sql伺服器名‘;‘使用者名稱‘; ‘密碼‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘sql伺服器名‘;‘使用者名稱‘; ‘密碼‘,資料庫名.架構名.表名)SELECT * FROM OPENROWSET(‘SQLOLEDB‘, ‘sql伺服器名‘;‘使用者名稱‘; ‘密碼‘,‘SELECT * FROM 資料庫名.架構名.表名‘)SELECT * FROM OPENROWSET(‘SQLNCLI‘, ‘sql伺服器名‘;‘使用者名稱‘; ‘密碼‘,‘SELECT * FROM 資料庫名.架構名.表名‘)    /* 例如：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)         或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)         或：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,‘SELECT * FROM Mydb.dbo.tb‘)         或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘server=192.168.58.208;uid=sa;pwd=123‘,‘SELECT * FROM Mydb.dbo.tb‘)        或：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘192.168.58.208‘;‘sa‘;‘123‘,mydb.dbo.tb)         或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘192.168.58.208‘;‘sa‘;‘123‘,mydb.dbo.tb)         或：SELECT * FROM OPENROWSET(‘SQLOLEDB‘,‘192.168.58.208‘;‘sa‘;‘123‘,‘SELECT * FROM Mydb.dbo.tb‘)         或：SELECT * FROM OPENROWSET(‘SQLNCLI‘,‘192.168.58.208‘;‘sa‘;‘123‘,‘SELECT * FROM Mydb.dbo.tb‘)     */--3.2.2 匯入樣本--3.2.2.1 匯入所有列INSERT OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名)SELECT * FROM 本地表    /* 例如：INSERT OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)            SELECT * FROM tb    */--3.2.2.2 匯入指定列INSERT OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名)(列,列...)SELECT 列,列... FROM 本地表    /* 例如：INSERT OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)(RANGE,LEVEL,Persons)            SELECT RANGE,LEVEL,Persons FROM tb    */--註：更多替代方法參考.2.1查詢樣本，只需替換行集合函式(OPENROWSET)內的內容即可。--3.2.3 更新樣本UPDATE OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名)SET 欄位=‘值‘WHERE 欄位=‘條件‘    /*例如：UPDATE OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)            SET Persons=‘g‘             WHERE Persons=‘a‘    */--註：更多替代方法參考.2.1查詢樣本，只需替換行集合函式(OPENROWSET)內的內容即可。--3.2.4 刪除樣本DELETE OPENROWSET(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘,資料庫名.架構名.表名) WHERE 欄位名=‘條件‘    /*例如：DELETE OPENROWSET(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘,mydb.dbo.tb)             WHERE Persons=‘g‘    */--註：更多替代方法參考.2.1查詢樣本，只需替換行集合函式(OPENROWSET)內的內容即可。 --3.3 OPENDATASOURCE方法(不需要用到建立好的連結名。如果串連的執行個體名不是預設的，需要在"sql伺服器名或IP地址"後加上"/執行個體名")--3.3.1 查詢樣本--3.3.1.1 Windows認證方式查詢(以下方法之一即可)SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘).資料庫名.架構名.表名SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;Trusted_Connection=yes‘).資料庫名.架構名.表名    /* 例如：SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘,‘Server=192.168.58.208;Trusted_Connection=yes‘).Mydb.dbo.tb         或：SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘,‘Server=192.168.58.208;Trusted_Connection=yes‘).Mydb.dbo.tb    */--3.3.1.2 SQL認證方式查詢(以下方法之一即可)SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘, ‘Data Source=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘, ‘Data Source=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名    /* 例如：SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘,‘Server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb         或：SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘,‘Server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb        或：SELECT * FROM OPENDATASOURCE(‘SQLOLEDB‘,‘Data Source=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb        或：SELECT * FROM OPENDATASOURCE(‘SQLNCLI‘,‘Data source=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb    */--3.3.2 匯入樣本--3.3.2.1 匯入所有列INSERT OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名SELECT * FROM 本地表    /* 例如：INSERT OPENDATASOURCE(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb            SELECT * FROM tb    */--3.3.2.2 匯入指定列INSERT OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名(列,列...)SELECT 列,列... FROM 本地表    /* 例如：INSERT OPENDATASOURCE(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb(RANGE,LEVEL,Persons)            SELECT RANGE,LEVEL,Persons FROM tb    */--註：更多替代方法參考.3.1查詢樣本，只需替換行集合函式(OPENDATASOURCE)內的內容即可。--3.3.3 更新樣本UPDATE OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名SET 欄位=‘值‘WHERE 欄位=‘條件‘    /*例如：UPDATE OPENDATASOURCE(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb            SET Persons=‘g‘             WHERE Persons=‘a‘    */--註：更多替代方法參考.3.1查詢樣本，只需替換行集合函式(OPENDATASOURCE)內的內容即可。--3.3.4 刪除樣本DELETE OPENDATASOURCE(‘SQLOLEDB‘, ‘server=sql伺服器名或IP地址;uid=使用者名稱;pwd=密碼‘).資料庫名.架構名.表名WHERE 欄位名=‘條件‘    /*例如：DELETE OPENDATASOURCE(‘SQLOLEDB‘,‘server=192.168.58.208;uid=sa;pwd=123‘).mydb.dbo.tb            WHERE Persons=‘g‘    */--註：更多替代方法參考.3.1查詢樣本，只需替換行集合函式(OPENDATASOURCE)內的內容即可。 --4. 刪除連結的伺服器方法--如果以後不再使用時可刪除連結資訊--4.1 刪除登入資訊(或叫刪除連結的伺服器登入名稱映射)EXEC sp_droplinkedsrvlogin ‘LinkName‘,NULL    /*例如：EXEC sp_droplinkedsrvlogin ‘TonyLink‘,NULL  */--4.2 刪除連結的伺服器名稱EXEC sp_dropserver ‘LinkName‘,‘droplogins‘ --如果指定droplogins，則在刪除連結的伺服器之前要刪除登入名稱映射    /*例如：EXEC sp_dropserver ‘TonyLink‘,‘droplogins‘ */   --附：擷取Provider Name的方法(EXEC master..xp_enum_oledb_providers)SELECT CAST([Provider Name] AS VARCHAR(30)) ProviderName,       CAST([Provider Description] AS VARCHAR(60)) ProviderDescriptionFROM OPENROWSET(    ‘SQLOLEDB‘,    ‘Server=.;Trusted_Connection=yes‘,    ‘SET FMTONLY OFF;     EXEC master..xp_enum_oledb_providers‘    )/*ProviderName                   ProviderDescription------------------------------ ------------------------------------------------------------SQLOLEDB                       Microsoft OLE DB Provider for SQL ServerDTSPackageDSO                  Microsoft OLE DB Provider for DTS PackagesSQLReplication.OLEDB           SQL Server Replication OLE DB Provider for DTSMSOLAP                         Microsoft OLE DB Provider for Analysis Services 10.0MSDMine                        Microsoft OLE DB Provider For Data Mining ServicesMicrosoft.ACE.OLEDB.12.0       Microsoft Office 12.0 Access Database Engine OLE DB ProviderADsDSOObject                   OLE DB Provider for Microsoft Directory ServicesSQLNCLI10                      SQL Server Native Client 10.0MSDAIPP.DSO                    Microsoft OLE DB Provider for Internet PublishingMSDASQL                        Microsoft OLE DB Provider for ODBC DriversMicrosoft.Jet.OLEDB.4.0        Microsoft Jet 4.0 OLE DB ProviderMSDAOSP                        Microsoft OLE DB Simple ProviderMSDAORA                        Microsoft OLE DB Provider for OracleMSIDXS                         Microsoft OLE DB Provider for Indexing Service (14 行受影響)*/ 

　　

SQL不同伺服器資料庫之間的資料操作整理(完整版)