SQL Server cross-Library query-distributed query [reprint]

--Connect remote SQL or insert data with OPENROWSET--if only temporary access, you can directly use OPENROWSET--Query ExampleSelect *  from OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name)--Import SampleSelect *  intoTable from OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name)--create a linked serverexecsp_addlinkedserver'Srv_lnk',"','SQLOLEDB','remote server name or IP address'execsp_addlinkedsrvlogin'Srv_lnk','false',NULL,'User name','Password'Go--Query ExampleSelect *  fromsrv_lnk. Database name. dbo. Table name--Import SampleSelect *  intoTable fromsrv_lnk. Database name. dbo. Table name--remove linked server when no longer in useexecSp_dropserver'Srv_lnk','droplogins'Go--The following example accesses data from a table that is in another instance of SQL Server. SELECT * from OpenDataSource('SQLOLEDB','Data Source=servername; User Id=myuid; Password=mypass'). Northwind.dbo.Categories--The following is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet. SELECT * from OpenDataSource('microsoft.jet.oledb.4.0','Data source= "C:financeaccount.xls"; User id=admin; password=; Extended Properties=excel 5.0') ... xactions

For example:SELECT * from OPENQUERY(Far_mdb,'SELECT * from Mdb_member'This is the data note for querying the Mdb_member table from my linked server:1, for more information about linked servers, see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_addserver, sp_dropserver, sp_serveroption, in Books Online, System-level stored procedures such as sp_linkedservers. 2, about the creation of this linked server and its use in different environments (all intranet servers, one intranet, a public network server, all public network server) test, are passed. 3, sometimes in the stored procedure to access the linked server may encounter the following prompts, you must set the ANSI warning and ANSI Nulls, this time as follows: Set the properties of the local database--> Connect-Select ANSI Warning and ANSI nullsHow to use SQL statements for cross-Library queries create a temporary table #tmp in the database database2 on machine B, which is the Table1 in the database database1 on machine A. How do I do it with SQL statements? (Do not borrow DTS, etc.) SQL code--create a linked serverexecsp_addlinkedserver'ITSV',"','SQLOLEDB','remote server name or IP address'execsp_addlinkedsrvlogin'ITSV','false',NULL,'User name','Password'--Query ExampleSelect *  fromitsv. Database name. dbo. Table name--Import SampleSelect *  intoTable fromitsv. Database name. dbo. Table name--remove linked server when no longer in useexecSp_dropserver'ITSV','droplogins'--connecting remote/LAN data (Openrowset/openquery/opendatasource)--1, OpenRowset--Query ExampleSelect *  from OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name)--Raw Cost surfaceSelect *  intoTable from OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name)--Importing a local table to a remote tableInsert OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name)Select * fromLocal Tables--Updating local tablesUpdatebSetB. Column A=A. Column A from OpenRowset('SQLOLEDB','SQL Server name';'User name';'Password', database name. dbo. Table name) asAInner JoinLocal Table B onA.column1=B.column1--OPENQUERY usage requires creating a connection--Create a linked server by creating a connection firstexecsp_addlinkedserver'ITSV',"','SQLOLEDB','remote server name or IP address'--EnquirySelect * from OpenQuery(ITSV,'SELECT * from database. dbo. Table name')--Importing a local table to a remote tableInsert OpenQuery(ITSV,'SELECT * from database. dbo. Table name')Select *  fromLocal Tables--Updating local tablesUpdatebSetB. Column B=A. Column B from OpenQuery(ITSV,'SELECT * from database. dbo. Table name') asaInner JoinLocal surface B onA. Column A=B. Column A--3, Opendatasource/openrowsetSELECT   * from   OpenDataSource('SQLOLEDB','Data Source=ip/servername; User id= Login name password= password'). Test.dbo.roy_ta--Importing a local table to a remote tableInsert OpenDataSource('SQLOLEDB','Data Source=ip/servername; User id= Login name password= password'). database. dbo. Table nameSelect *  fromlocal tables with linked serversOPENROWSET OpenDataSourcecan be linked to a server. Database1.dbo.table1 linked server. Database1.dbo.table1SQL CodeSelect *  into # from OpenRowset('SQLOLEDB','IP';'SA';"','SELECT * from Pubs.dbo.jobs'If you use OpenDataSource only occasionally/OpenRowsetFixed frequent use of built linked server

SQL Server cross-Library query-distributed query [reprint]