SQL Azure - 在数据库之间复制表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6572754/
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
SQL Azure - copy table between databases
提问by Alex Dybenko
I am trying to run following SQL:
我正在尝试运行以下 SQL：
INSERT INTO Suppliers ( [SupplierID], [CompanyName])
Select [SupplierID], [CompanyName] From [AlexDB]..Suppliers
and got an error "reference to database and/or server name in is not supported in this version of sql server"
并收到错误“此版本的 sql server 不支持对数据库和/或服务器名称的引用”
Any idea how to copy data between databases "inside" the server? I can load data to client and then back to server, but this is very slow.
知道如何在服务器“内部”的数据库之间复制数据吗？我可以将数据加载到客户端，然后返回到服务器，但这非常慢。
回答by Mark Jones
I know this is old, but I had another manual solution for a one off run.
我知道这已经过时了，但我有另一种手动解决方案来解决问题。
Using SQL Management Studio R2 SP1 to connect to azure, I right click the source database and select generate scripts.
使用 SQL Management Studio R2 SP1 连接到 azure，我右键单击源数据库并选择生成脚本。
during the wizard, after I have selected my tables I select that I want to output to a query window, then I click advanced. About half way down the properties window there is an option for "type of data to script". I select that and change it to "data only", then I finish the wizard.
在向导过程中，选择我的表后，我选择要输出到查询窗口，然后单击高级。在属性窗口的大约一半处，有一个“脚本数据类型”选项。我选择它并将其更改为“仅数据”，然后我完成了向导。
All I do then is check the script, rearrange the inserts for constraints, and change the using at the top to run it against my target DB.
然后我要做的就是检查脚本，重新排列插入的约束，并更改顶部的 using 以针对我的目标数据库运行它。
Then I right click on the target database and select new query, copy the script into it, and run it.
然后我右键单击目标数据库并选择新查询，将脚本复制到其中，然后运行它。
Done, Data migrated.
完成，数据迁移。
hope that helps someone
希望能帮助某人
回答by Magu
Since 2015, this can be done by use of elastic database queriesalso known as cross database queries.
自 2015 年以来，这可以通过使用弹性数据库查询（也称为跨数据库查询）来完成。
I created and used this template, it copies 1.5 million rows in 20 minutes:
我创建并使用了这个模板，它在 20 分钟内复制了 150 万行：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = '<username>',
SECRET = '<password>';
CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
TYPE=RDBMS,
LOCATION='<server>.database.windows.net',
DATABASE_NAME='<db>',
CREDENTIAL= SQL_Credential
);
CREATE EXTERNAL TABLE [dbo].[source_table] (
[Id] BIGINT NOT NULL,
...
)
WITH
(
DATA_SOURCE = RemoteReferenceData
)
SELECT *
INTO target_table
FROM source_table
回答by Terence Siganakis
Unfortunately there is no way to do this in a single query.
不幸的是，没有办法在单个查询中做到这一点。
The easiest way to accomplish it is to use "Data Sync" to copy the tables. The benefit of this is that it will also work between servers, and keep your tables in sync.
完成它的最简单方法是使用“数据同步”来复制表。这样做的好处是它还可以在服务器之间工作，并使您的表保持同步。
http://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started-sql-data-sync/
http://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started-sql-data-sync/
In practise, I haven't had that great of an experience with "Data Sync" running in production, but its fine for once off jobs.
实际上，我对在生产中运行的“数据同步”没有那么好的经验，但它可以用于一次性工作。
One issue with "Data Sync" is that it will create a large number of "sync" objects in your database, and deleting the actual "Data Sync" from the Azure portal may or may not clean them up. Follow the directions in this article to clean it all up manually:
“数据同步”的一个问题是它会在您的数据库中创建大量“同步”对象，从 Azure 门户中删除实际的“数据同步”可能会也可能不会清理它们。按照本文中的说明手动清理所有内容：
https://msgooroo.com/GoorooTHINK/Article/15141/Removing-SQL-Azure-Sync-objects-manually/5215
https://msgooroo.com/GoorooTHINK/Article/15141/Removing-SQL-Azure-Sync-objects-manually/5215
回答by Vignesh M
Use the following steps, there is no straight forward way to do so. But by some trick we can.
使用以下步骤，没有直接的方法可以这样做。但是通过一些技巧我们可以。
Step1 : Create another one table with the same structure of Suppliers table inside [AlexDB], Say it as SuppliersBackup
Step1：在[AlexDB]中创建另一个与Suppliers表结构相同的表，说它是SuppliersBackup
Step2 : Create table with the same structure of Suppliers table inside DesiredDB
Step2：在DesiredDB中创建与Suppliers表结构相同的表
Step3 : Enable Data Sync Between AlexDB..Suppliers and DesiredDB..Suppliers
步骤 3：启用 AlexDB..Suppliers 和 DesiredDB..Suppliers 之间的数据同步
Step4 : Truncate data from AlexDB..Suppliers
Step4：截断来自 AlexDB..Suppliers 的数据
Step5 : Copy data from AlexDB..SuppliersBackup to AlexDB..Suppliers
Step5：将数据从 AlexDB..SuppliersBackup 复制到 AlexDB..Suppliers
Step6 : Now run the sync
第6步：现在运行同步
Data Copied to DesiredDB.
数据复制到 DesiredDB。
回答by IUnknown
SQL-Azure does not support USE statement and effectively no cross-db queries. So the above query is bound to fail.
SQL-Azure 不支持 USE 语句并且实际上没有跨数据库查询。所以上面的查询肯定会失败。
If you want to copy/backup the db to another sql azure db you can use the "Same-server" copying or "Cross-Server" copying in SQL-Azure. Refer this msdn article
如果要将数据库复制/备份到另一个 sql azure 数据库，您可以使用 SQL-Azure 中的“相同服务器”复制或“跨服务器”复制。参考这篇 msdn 文章
回答by Richard Mitchell
You could use a tool like SQL Data Comparefrom Red Gate Software that can move database contents from one place to another and fully supports SQL Azure. 14-day free trial should let you see if it can do what you need.
您可以使用Red Gate Software 的SQL Data Compare 之类的工具，该工具可以将数据库内容从一个地方移动到另一个地方，并完全支持 SQL Azure。14 天免费试用应该让您看看它是否可以满足您的需求。
Full disclosure: I work for Red Gate Software
完全披露：我为 Red Gate Software 工作
回答by Thomas
If you have onprem version that has the sp_addlinkedsrvlogin, you can setup Linked Servers for both source and target database then you can run your insert into query.
如果您有具有 sp_addlinkedsrvlogin 的本地版本，您可以为源数据库和目标数据库设置链接服务器，然后您可以运行插入查询。
See "SQL Server Support for Linked Server and Distributed Queries against Windows Azure SQL Database" in this blog: https://azure.microsoft.com/en-us/blog/announcing-updates-to-windows-azure-sql-database/
请参阅此博客中的“针对 Windows Azure SQL 数据库的链接服务器和分布式查询的 SQL Server 支持”：https: //azure.microsoft.com/en-us/blog/annoucing-updates-to-windows-azure-sql-database /
回答by ozz
An old post, but another option is the Sql Azure Migration wizard
旧帖子，但另一个选项是Sql Azure 迁移向导
回答by Alex Dybenko
Ok, i think i found answer - no way. have to move data to client, or do some other tricks. Here a link to article with explanations: Limitations of SQL Azure: only one DB per connectionBut any other ideas are welcome!
好的，我想我找到了答案 - 没办法。必须将数据移动到客户端，或者做一些其他的技巧。这里有一个带有解释的文章链接：SQL Azure 的限制：每个连接只有一个数据库但欢迎任何其他想法！
回答by Vad
A few options (rather workarounds):
一些选项（而不是解决方法）：
- Generate script with data
- Use data sync in Azure
- Use MS Access (import and then export), with many exclusions (like no GUID in Access)
- Use 3-rd party tools like Red Gate.
- 用数据生成脚本
- 在 Azure 中使用数据同步
- 使用 MS Access（导入然后导出），有很多排除项（比如 Access 中没有 GUID）
- 使用 Red Gate 等 3 方工具。
Unfortunately no easy and built-in way to do that so far.
不幸的是，到目前为止还没有简单的内置方法可以做到这一点。
