SQL statements connect to other servers

--Create a linked server
exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '
--query Example
SELECT * from ITSV. Database name. dbo. Table name
--Remove linked server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

Data operations between different server databases--Create a linked server exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address ' exec sp_addlinkedsrvlogin ' I TSV ', ' false ', NULL, ' User name ', ' Password '--query Example SELECT * from ITSV. Database name. dbo. Table name--import Example select * into table from ITSV. Database name. dbo. Table name--To After you no longer use the Delete link server exec sp_dropserver ' itsv ', ' droplogins '--connect remote/LAN data (Openrowset/openquery/opendatasource)--1, OPENROWSET--query Example SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)--Raw cost surface select * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name--Import 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 updated B set B. Column a=a. Column A from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B on A.column1=b.column1--openquery usage need to create a connection--first create a connection to create a linked server exec Sp_addlinkedse RVer ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--query select * FROM OPENQUERY (ITSV, ' select * from database. dbo. Table name ')--the local table guide Into the remote table Insert OpenQuery (ITSV, 'SELECT * FROM database. dbo. Table name ') SELECT * from local table-Updated local table update B set b. column b=a. Column B from OPENQUERY (ITSV, ' select * from database. dbo . Table name ') as a inner join local table B on a. Column a=b. Column A--3, opendatasource/openrowset SELECT * from OpenDataSource (' SQLOLEDB ', ' Data Source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta--Import the local table into the remote table Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name SELECT * FROM

SQL statement to connect to other servers