SQL statement (CODE) for linking databases on different servers)

 

SQL code

Code highlighting produced by actipro codehighlighter (freeware) implements sp_add+server 'itsv', '', 'sqlodb', 'remote server name or IP address 'exec sp_add+srvlogin 'itsv', 'false ', null, 'username', 'Password' -- Query example select * From itsv. database Name. DBO. table name -- import example select * into table from itsv. database Name. DBO. table name -- delete the linked servers exec sp_dropserver 'itsv' and 'droplogins' when they are no longer in use -- connect to the remote/LAN data (OpenRowSet/openquery/OpenDataSource) -- 1. OpenRowSet -- Query example select * From OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. table Name) -- generate a local table select * into table from OpenRowSet ('sqlodb', 'SQL Server name', 'username', 'Password', database name. DBO. table Name) -- import the local table into the remote table insert OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. table Name) Select * from local table -- update local table update bset B. column A =. column afrom OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. table Name) as a inner join local table Bon. column1 = B. column1 -- create a connection for openquery usage -- first create a connection to create the connection server exec sp_add+server 'itsv', '', 'sqloledb ', 'remote server name or IP address' -- Query select * From openquery (itsv, 'select * from database. DBO. table name ') -- import the local table to the remote table insert openquery (itsv, 'select * from database. DBO. table name ') Select * from local table -- update local table update bset B. column B =. column bfrom openquery (itsv, 'select * from database. DBO. table name ') as ainner join local table B on. column A = B. columns A--3, OpenDataSource/openrowsetselect * From OpenDataSource ('sqlodb', 'Data source = IP/servername; user id = login name; Password = password '). test. DBO. roy_ta -- import the local table to the remote table insert OpenDataSource ('sqlodb', 'Data source = IP/servername; user id = login name; Password = password '). database. DBO. table Name select * from local table