--"Method 1" Connection server method--STEP1 create a linked server exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' IP address ' exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' SQL login name ', ' Password ' --exec sp_addlinkedserver ' srv_lnk ', ', ' SQLOLEDB ', ' 192.168.1.58 ' --exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' sa ', ' 123456 ' -- STEP2 Application: Query the SELECT * from Srv_lnk. Database name. dbo. Table --select * from srv_lnk.erp.dbo.t1192 - -STEP2 Application: Import select * into table from Srv_lnk. Database name. dbo. Table name select * into TTT from SRV_LNK.ERP.DBO.T1192 --STEP3 Delete connection: Remove linked server after no longer used exec sp_dropserver ' Srv_lnk ', ' droplogins ' --connect remote/LAN data (openrowset/ Openquery/opendatasource) --"Method 2" 2.1, openrowset --query Example select * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) ---SELECT * FROM OPENROWSET (' SQLOLEDB ', ' 192.168.1.58 '; ' Sa '; ' 123123 ', erp.dbo.t1192) --cost surface select * into table from OpenrowseT (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name, dbo. Table name --The local table into the remote table insert OpenRowset (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) Select *from Local table --update local table update b set B. Column a=a. Column a from OpenRowset (' SQLOLEDB ', ' sq L Server name '; ' User name '; ' Password ', database name. dbo. Table name) as A inner join local table B on a.column1=b.column1 --"Method 3" 2.2 OpenQuery usage need to create a connection --first create a connection to create a linked server exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address ' -- exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' 192.168.1.58 ' --query select * from Openq Uery (Srv_lnk, ' SELECT * from database. dbo. Table name ') --select * from OPENQUERY (srv_lnk, ' select * from test.dbo.t10 xx ') --the local table into the remote table insert openquery (srv_lnk, ' SELECT * from database. dbo. Table name ') SELECT * from local table & nbsp;--Update local table update B set B. column b=a. Column B from openquery (srv_lnk, ' SELECT * from database. dbo. Table name ') as a inner Join local Table B on A. Column a=b. Column a --"Method 4" 2.3, Opendatasource/openrowset select * from OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Database name. dbo. Table name select * from OpenDataSource (' SQLOLEDB ', ' Date source=192.168.1.58;user id=sa; password=123456 '). Test.dbo.h1000SqlServer distributed Query
SQL distributed queries, cross-database queries