How to Write cross-server SQL statements

 

 

Select *
From OpenDataSource (
'Sqlodb ',
'Data source = service name; user id = user; Password = password'
). Database Name. DBO. Table Name

---------------------------------------------------------------

Select * into local database name .. table name from OpenDataSource (
'Sqlodb ',
'Data source = remote IP address; user id = sa; Password = password'
). Database Name. DBO. Table Name

Insert local database name .. table name from OpenDataSource (
'Sqlodb ',
'Data source = remote IP address; user id = sa; Password = password'
). Database Name. DBO. Table Name

Or use the connection Server:
Exec sp_addmediaserver
'Alias ',
'',
'Msdasql ',
Null,
Null,
'Driver = {SQL Server}; server = remote name; uid = user; Pwd = password ;'
Go
Then you can:
Select * from alias. Database Name. DBO. Table Name
Insert database name. DBO. Table Name select * from alias... database name. DBO. Table Name
Select * into database name. DBO. New table name from alias... database name. DBO. Table Name
Go