SQL distributed queries, cross-database queries

--"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