在SERVER1上:
USE master
GO
EXEC sp_addlinkedserver
'server2',
N'SQL Server'
GOEXEC sp_addlinkedsrvlogin 'sserver2', 'false', NULL, 'Sa', 'pass'
GOSELECT * FROM server2.dbase2.dbo.table2 as A
left jion table1 as B on A.id = B.id
USE master
GO
EXEC sp_addlinkedserver
'server2',
N'SQL Server'
GOEXEC sp_addlinkedsrvlogin 'sserver2', 'false', NULL, 'Sa', 'pass'
GOSELECT * FROM server2.dbase2.dbo.table2 as A
left jion table1 as B on A.id = B.id
EXEC sp_addlinkedserver
'别名',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
GO
然后你就可以如下:
select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go