Data manipulation between different SQL Server databases (full version)

This article original website: http://blog.csdn.net/htl258/article/details/5695391

------------------------------------------------------------------------------------author:htl258 (Tony)--Date: 2010-06-25 22:23:18--version:microsoft SQL Server (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 (reprint retains this information)--subject:sql data manipulation between different server databases-------------------- ----------------------------------------------------------------1. Create a linked server--1.1 create a link name EXEC sp_addlinkedserver ' linkname ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--there is a custom instance name to add "/instance Name"/* For example: EXEC sp_ Addlinkedserver ' Tonylink ', ' ', ' SQLOLEDB ', ' 192.168.58.208 ' */--1.2 Create login information (or call to create a linked server login mapping) (just choose one way)--1.2.1 Log in as Windows certified exec sp_addlinkedsrvlogin ' linkname '-or exec sp_addlinkedsrvlogin ' linkname ', ' true '/* For example: EXEC Sp_addli                                                      Nkedsrvlogin ' tonylink ' */--1.2.2 log in as SQL authentication              exec sp_addlinkedsrvlogin ' linkname ', ' false ', NULL, ' username ', ' password '/* For example: exec sp_addlinkedsrvlogin ' tonylink ', ' F Alse ', null, ' sa ', ' 123 ' */--2. Linked server-related data Operations--2.1 query Example SELECT * from linkname. Database name. Name of the schema. Table names/* For example: SELECT * from TONYLINK.MYDB.DBO.TB */--2.2 Import Example SELECT * in To table name from Linkname. The database name. The name of the schema. Table name/* For example: SELECT * into NEWTB from TONYLINK.MYDB.DBO.TB */--2.3 Update example updates linkname. Database name. Schema name . table name SET field = ' value ' WHERE field = ' condition '/* For example: UPDATE TonyLink.Mydb.dbo.tb SET persons= ' g ' WHERE persons= ' a ' */--2.4 remove example Delete Link Name. Database name. The name of the schema. Table name where field name = ' condition '/* For example: DELETE TonyLink.Mydb.dbo.tb WHERE persons= ' g ' */--3. --3.1 OPENQUERY method through the rowset function (Openquery/openrowset/opendatasource) Action method (requires the linked server you just created):--3.1.1 Query Example SELECT * FROM OPENQUERY (Linkname, ' select * from database name. Schema name ')/* * For example: SELECT * from OPENQUERY (tonylink, ' select * from MYDB.DBO.TB ') */--3.1.2 guide Into the example--3.1.2.1 import all Columns INSERT OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') SELECT * from local table/* For example: INSERT OPENQUERY (Ton Ylink, ' SELECT * from MYDB.DBO.TB ') SELECT * from TB */--3.1.2.2 Import the specified column Insert OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') (columns, Columns ...) SELECT column, column ... from local table/* For example: INSERT OPENQUERY (Tonylink, ' select * from MYDB.DBO.TB ') (range,level,persons) SELECT Range,leve L,persons from TB */--3.1.3 Updates sample update OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') SET field = ' value ' WHERE field = ' condition '/* Example: UPDATE OPENQUERY (Tonylink, ' SELECT * from MYDB.DBO.TB ') SET persons= ' G ' WHERE persons= ' a ' */--3.1.4 Delete sample Delete Openque  RY (linkname, ' select * from database name. Schema name. Table name ') WHERE field name = ' condition '/* For example: DELETE OPENQUERY (Tonylink, ' select * from MYDB.DBO.TB ') WHERE persons= ' g ' */--3.2 OpenRowset method (does not need to be used to create a good link name.) If the instance name of the connection is not the default, you need to add "/instance Name" after "SQL Server name or IP address")--3.2.1 Query example--3.2.1.1 Windows Authentication Mode query (one of the following methods) SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; Trusted_connection=yes ', database name. Schema name. Table name) SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes ', database name. Schema name. Table name) SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; TrUsted_connection=yes ', ' select * from database name. Schema name. Table name ') SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes ', ' SELECT * from database name. Schema name. Table name ')/* For example: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes ', MYDB.DBO.TB) or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes ', MYDB.DBO.TB) or: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes ', ' select * from MYDB.DBO.TB ') or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes ', ' select * from MYDB.DBO.TB ') */--3.2.1.2 SQL authentication method query (one of the following methods) SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address, uid= user name, pwd= password ', database name, name of the schema. Table name) SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; uid= User name, pwd= password ', database name, name of the schema. Table name) SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', ' SELECT * from Database name. Schema name. Table name ') SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; uid= user name; pwd= password ', ' SELECT * from numberThe name of the library. The name of the schema. Table name ') SELECT * from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name, name of the schema. Table name) SELECT * from OPENROWSET (' SQLNCLI ', ' SQL Server name '; User name '; ' Password ', database name, name of the schema. Table name) SELECT * from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', ' select ' from database name. Schema name. Table name ') SELECT * from OPENROWSET (' SQLNCLI ', ' SQL Server name '; User name '; ' Password ', ' SELECT ' from the database name. Name of the schema. '/* For example: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', myd B.DBO.TB) or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) or: Selec  T * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', ' select * from MYDB.DBO.TB ') or: SELECT * FROM OPENROWSET (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 ', ' select * from MYDB.DBO.TB ') or: SELECT * from Openrowse T (' SQLOLEDB ', ' 192.168.58.208 '; Sa '; ' 123 ', MYDB.DBO.TB) or: SELECT * from OPENROWSET (' sqlncli ', ' 192.168.58.208 '; ') Sa '; ' 123 ', MYDB.DBO.TB) or: SELECT * from OPENROWSET (' SQLOLEDB ', ' 192.168.58.208 '; ') Sa '; ' 123 ', ' SelecT * from MYDB.DBO.TB ') or: SELECT * from OPENROWSET (' sqlncli ', ' 192.168.58.208 '; ') Sa '; ' 123 ', ' SELECT * from MYDB.DBO.TB ') */--3.2.2 Import example--3.2.2.1 import all columns Insert OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= User name, pwd= password ', database name, name of the schema. Table name) SELECT * from local table/* For example: INSERT OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) SELECT * from TB */--3.2.2.2 Import the specified column Insert OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; PW d= password ', database name. Schema name. Table name) (column, column ...) SELECT column, column ... from local table/* For example: INSERT OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) (Range,level, Persons) SELECT range,level,persons from TB */--Note: More alternative reference. 2.1 Query example, just replace the contents of the rowset function (OPENROWSET). --3.2.3 Update Example Updates OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name) SET field = ' value ' WHERE field = ' condition '/             * For example: UPDATE OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) SET persons= ' g ' WHERE persons= ' A ' */--Note: More alternative reference. 2.1 Query example,Just replace the content within the rowset function (OPENROWSET). --3.2.4 Remove Example Delete OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Name of the schema.) WHERE Field name = ' Condition '/* For example: DELETE OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) WHERE persons= ' G ' */--note : more alternative Reference. 2.1 Query example, just replace the contents of the rowset function (OPENROWSET). --3.3 OpenDataSource Method (you do not need to use the created link name.) If the instance name of the connection is not the default, you need to add "/instance Name" after "SQL Server name or IP address")--3.3.1 Query example--3.3.1.1 Windows Authentication Mode query (one of the following methods) SELECT * from OpenDataSource ( ' SQLOLEDB ', ' server=sql server name or IP address; Trusted_connection=yes '). The database name. Schema name. Table Name SELECT * from OpenDataSource (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes '). Database name. Schema name. Table name/* For example: SELECT * from OpenDataSource (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes '). MYDB.DBO.TB or: SELECT * from OpenDataSource (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes '). MYDB.DBO.TB */--3.3.1.2 SQL authentication Mode query (one of the following methods) SELECT * from OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd = Password '). The database name. Schema name. Table Name SELECT * from OpenDataSource (' SQLNcli ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. The name of the schema. Table name select * from OpenDataSource (' SQLOLEDB ', ' Data source= SQL Server name or IP address; uid= user name; pwd= password '). The database name. The name of the schema. Table name select * from OpenDataSource (' sqlncli ', ' Data source=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table name/* For example: SELECT * from OpenDataSource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB or: SELECT * from OpenDataSource (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB or: SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB or: SELECT * from OP Endatasource (' sqlncli ', ' Data source=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB */--3.3.2 Import Example--3.3.2.1 Import all columns Insert OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. The name of the schema. Table name select * FROM local table/* For example: INSERT opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB SELECT * from TB */- -3.3.2.2 Import the specified column Insert OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= Password '). The database name. Schema name. Table name (column, column ...) SELECT column, column ... from local table/* For example: INSERT opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB (Range,level, Persons) SELECT range,level,persons from TB */--Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE). --3.3.3 updates The example update OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. The name of the schema. Table name Set field = ' value ' WHERE field = ' Conditions '/* For example: UPDATE opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB SET persons= ' g ' WHERE persons= ' a ' */--Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE). --3.3.4 Deletes the example delete opendatasource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. The name of the schema. Table name where field name = ' Condition '/* For example: DELETE opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB WHERE persons= ' g ' */ --Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE). --4. Remove linked server Method--Delete link information if you are no longer in use--4.1 Delete login information (or call delete link Server login mapping) exec sp_droplinkedsrvlogin ' linkname ', NULL/* For example: EXEC SP_droplinkedsrvlogin ' Tonylink ', NULL */--4.2 remove the linked server name exec sp_dropserver ' linkname ', ' droplogins '--if droplogins is specified, Delete the login mappings before deleting the linked server/* For example: exec sp_dropserver ' tonylink ', ' droplogins ' */--attached: method to get Provider name (EXEC master. xp_enum_oledb_providers) SELECT cast ([Provider Name] as VARCHAR ()) ProviderName, CAST ([Provider Description] as VAR CHAR (()) 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 serverd Tspackagedso Microsoft OLE DB Provider for DTS packagessqlreplication.oledb SQL Server replicat                        Ion 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 S QL Server Native Client 10.0MSDAIPP. DSO Microsoft OLE DB Provider for Internet publishingmsdasql Microsoft OLE DB Pr Ovider for ODBC driversmicrosoft.jet.oledb.4.0 Microsoft Jet 4.0 OLE DB providermsdaosp MICR                         Osoft OLE DB simple Providermsdaora Microsoft OLE db Provider for ORACLEMSIDXS  Microsoft OLE DB Provider for indexing Service (14 rows affected) */

　　

Data manipulation between different SQL Server databases (full version)