SQL 将数据从一台服务器插入到另一台服务器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3285391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data From One Server To Another?
提问by sooprise
If I want to run this sort of query in SQL Server, how can I do the same query from one server I am connected to to another?
如果我想在 SQL Server 中运行此类查询，如何从我连接到另一台服务器的服务器执行相同的查询？
I tried adding "[ServerName1]." before "[DatabaseName1].[dbo]..." and "[ServerName2]." before "[DatabaseName2].[dbo]..." but that didn't seem to work.
我尝试添加“[ServerName1]”。在“[DatabaseName1].[dbo]...”和“[ServerName2]”之前。在“[DatabaseName2].[dbo]...”之前，但这似乎不起作用。
INSERT INTO [DatabaseName1].[dbo].[TableName]
([FieldName])
SELECT [FieldName] FROM [DatabaseName2].[dbo].[TableName]
Is this possible?
这可能吗？
回答by HLGEM
Yes you would use the server-name before the whole rest of object-name like:
是的，您会在整个对象名称的其余部分之前使用服务器名称，例如：
myserver.mydatabase.dbo.mytable
However you first have to set up linked servers. Look up linked servers in BOL.
但是，您首先必须设置链接服务器。在 BOL 中查找链接服务器。
回答by Martin Smith
If you have adhoc distributed queriesenabled you can use OPENDATASOURCE. Setting up a linked server is another option. Not sure of the pros and cons of each approach.
如果您启用了即席分布式查询，则可以使用OPENDATASOURCE。设置链接服务器是另一种选择。不确定每种方法的优缺点。
INSERT INTO [DatabaseName1].[dbo].[TableName]
SELECT FieldName
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=Server\InstanceName;Integrated Security=SSPI')
.DatabaseName2.dbo.TableName
回答by SoftwareGeek
The best way to do this would be to create a "linked server" between the two. You will need appropriate permissions to do this.
最好的方法是在两者之间创建一个“链接服务器”。您将需要适当的权限才能执行此操作。
Then it's just a matter of accessing the databases using your linkedserver name.
那么这只是使用您的链接服务器名称访问数据库的问题。
Ex: [linkedserver].databasename.dbo.tablename
例如：[linkedserver].databasename.dbo.tablename
To create a linkedserver, go to server objects->right click on linked servers->click on 'new linked server'.
要创建链接服务器，请转到服务器对象-> 右键单击链接服务器-> 单击“新链接服务器”。
