SQL 跨服务器SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/70455/
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
Cross-server SQL
提问by Boris Callens
I want to port data from one server's database to another server's database. The databases are both on a different mssql 2005 server. Replication is probably not an option since the destination database is generated from scratch on a [time interval] basis.
我想将数据从一台服务器的数据库移植到另一台服务器的数据库。数据库都在不同的 mssql 2005 服务器上。复制可能不是一种选择，因为目标数据库是在 [时间间隔] 基础上从头生成的。
Preferebly I would do something like
最好我会做类似的事情
insert *
from db1/table1
into db2/table2
where rule1 = true
It's obvious that connection credentials would go in somehwere in this script.
很明显，连接凭据会出现在此脚本中。
回答by Matthew Pelser
回答by Ardalan Shahgholi
You can use Open Data Source Like this :
您可以像这样使用开放数据源：
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT *
FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=<Ip Of Your Server>;
User ID=<SQL User Name>;Password=<SQL password>').<DataBase name>.<SchemaName>.<Table Or View Name>
Go
回答by kristof
Would you be transferring the whole content of the database from one server to another or just some data from a couple of tables?
您是要将数据库的全部内容从一台服务器传输到另一台服务器，还是只是将几个表中的一些数据传输？
For both options SSIS would do the job especially if you are planning to to the transfer on a regular basis.
对于这两种选择，SSIS 都可以胜任，特别是如果您打算定期进行转移。
If you simply want to copy some data from 1 or 2 tables and prefer to do it using TSQL in SQL Management Studio then you can use linked server as suggested by pelser
如果您只是想从 1 或 2 个表中复制一些数据，并且更喜欢在 SQL Management Studio 中使用 TSQL 来完成，那么您可以按照 pelser 的建议使用链接服务器
- Set up the source database server as a linked server
- Use the following syntax to access data
- 将源数据库服务器设置为链接服务器
- 使用以下语法访问数据
select columnName1, columnName2, etc from serverName.databaseName.schemaName.tableName回答by Philippe Grondier
Well I don't agree with your comment on replication. You can start a replication by creating a database from scratch, and you can control either the updates will be done by updating the available client database or simply recreating the database.
好吧，我不同意您对复制的评论。您可以通过从头开始创建数据库来启动复制，并且您可以控制更新是通过更新可用的客户端数据库来完成还是简单地重新创建数据库。
Automated replication will ease your work by automatically managing keys and relations.
自动复制将通过自动管理密钥和关系来简化您的工作。
I think the easiest thing to do is to start a snapshot replication through MSSQL Server Studio, get the T-SQL corresponding scripts (ie the corresponding T-SQL instructions for both publication and subscriptions), and record these scripts as part of a job in the Jobs list of the SQL Agent or as a replication job in the replications folder.
我认为最简单的做法是通过MSSQL Server Studio启动快照复制，获取T-SQL对应的脚本（即发布和订阅对应的T-SQL指令），并将这些脚本作为作业的一部分记录在SQL 代理的作业列表或作为复制文件夹中的复制作业。
回答by Philippe Grondier
You could go the linked server route.
你可以去链接服务器路由。
you just can't use the select * into you have to do an insert into select.
你只是不能使用 select * into 你必须插入到 select 中。
I would avoid replication if you don't have experience with it as it can be difficult to fix if it breaks and can be prone to other problems if not properly managed.
如果您没有经验，我会避免复制，因为如果它损坏可能难以修复，并且如果管理不当可能容易出现其他问题。
Keep it simple especially if the databases are small.
保持简单，尤其是在数据库很小的情况下。
回答by Galwegian
Are SQL Server Integration Services (SSIS) an option? If so, I'd use that.
SQL Server 集成服务 (SSIS) 是一种选择吗？如果是这样，我会使用它。
回答by Thomas Rushton
回答by Ahmed Tambe
CREATE VIEW newR1
AS
SELECT * from OPENQUERY ([INSTANCE_NAME], 'select * from DbName.SchemaName.TableName')
