使用“选择进入".使用Azure SQL从另一个数据库复制数据 [英] Using "SELECT INTO" with Azure SQL to copy data from another DB

问题描述

我正在尝试在Azure上自动初始化SQL DB.对于某些(查找)表，每次初始化时都需要将数据从源数据库复制到新数据库中.

I'm trying to automate the initialising of a SQL DB on Azure. For some (lookup) tables, data needs to be copied from a source DB into the new DB each time it is initialised.

为此，我执行一个包含

SELECT * INTO [target_db_name]..[my_table_name] FROM [source_db_name].dbo.[my_table_name]

这时抛出一个异常告诉我

At this point an exception is thrown telling me that

"source_db_name.dbo.my_table_name"中对数据库和/或服务器名称的引用 此版本的SQL Server不支持.

Reference to database and/or server name in 'source_db_name.dbo.my_table_name' is not supported in this version of SQL Server.

对此进行了研究，我发现现在可以引用另一个Azure SQL DB，只要它已被配置为外部数据源. [此处和此处]

Having looked into this, I've found that it's now possible to reference another Azure SQL DB provided it has been configured as an external data source. [here and here]

因此，在目标数据库中，我执行了以下语句:

So, in my target DB I've executed the following statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL cred  
WITH IDENTITY = '<username>',
SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE [source_db_name]
WITH
(
    TYPE=RDBMS,
    LOCATION='my_location.database.windows.net',
    DATABASE_NAME='source_db_name',
    CREDENTIAL= cred
);

CREATE EXTERNAL TABLE [dbo].[my_table_name](
    [my_column_name] BIGINT NOT NULL
)
WITH
(
    DATA_SOURCE = [source_db_name],
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'my_table_name'
)

但是SELECT INTO语句仍然会产生相同的异常.

But the SELECT INTO statement still yields the same exception.

此外，简单的SELECT * FROM [source_db_name].[my_table_name]会产生异常无效的对象名称'source_db_name.my_table_name'".

Furthermore, a simple SELECT * FROM [source_db_name].[my_table_name] yields the exception "Invalid object name 'source_db_name.my_table_name'".

我想念什么?

更新
我发现了问题:CREATE EXTERNAL TABLE在目标数据库中创建看起来像的表.要进行查询，不应使用源数据库名称.所以我失败了:

UPDATE
I've found the problem: CREATE EXTERNAL TABLE creates what appears to be a table in the target DB. To query this, the source DB name should not be used. So where I was failing with:

SELECT * FROM [source_db_name].[my_table_name]

我看到我应该在查询

SELECT * FROM [my_table_name]

推荐答案

按照承诺，这是我处理SQL Server数据库部署的方式.我对本地，Windows Azure SQL数据库或Azure中VM上的SQL使用相同的方法.经历了很多痛苦，反复试验.

As promised, here's how I handle database deploys for SQL Server. I use the same method for on-prem, Windows Azure SQL Database, or SQL on a VM in Azure. It took a lot of pain, trial and error.

这一切都始于SQL Server数据工具SSDT 如果尚未使用SSDT将数据库作为与应用程序分开的项目来管理数据库，则需要这样做.在此处获取副本.如果您已经在计算机上运行了Visual Studio版本，则可以获得特定于该版本Visual Studio的SSDT版本.如果您还没有运行VS，那么只需抓住SSDT，它就会安装最少的Visual Studio组件来帮助您前进.

It all starts with SQL Server Data Tools, SSDT If you're not already using SSDT to manage your database as a project separate from your applications, you need to. Grab a copy here. If you are already running a version of Visual Studio on your machine, you can get a version of SSDT specific for that version of Visual Studio. If you aren't already running VS, then you can just grab SSDT and it will install the minimal Visual Studio components to get you going.

设置第一个数据库项目很容易！启动一个新的数据库项目.

Setting up your first Database project is easy! Start a new Database project.

然后，右键单击您的数据库项目，然后选择Import-> Database.

Then, right click on your database project and choose Import -> Database.

现在，您可以指向数据库的当前开发副本，并将其架构导入到您的项目中.此过程将从源数据库中提取所有表，视图，存储过程，函数等.完成后，您将看到类似下图的内容.

Now, you can point at your current development copy of your database and import it's schema into your project. This process will pull in all the tables, views, stored procedures, functions, etc from the source database. When you're finished you will see something like the following image.

对于每个导入的模式都有一个文件夹，以及一个用于在数据库中定义模式的安全文件夹.浏览这些文件夹并浏览创建的文件.

There is a folder for each schema imported, as well as a security folder for defining the schemas in your database. Explore these folders and look through the files created.

您会发现创建的所有脚本都是CREATE脚本.对于管理项目，记住这一点很重要.现在，您可以保存新的解决方案，然后将其检入当前的源代码管理系统.这是您的初次提交.

You will find all the scripts created are the CREATE scripts. This is important to remember for managing the project. You can now save your new solution, and then check it into your current source control system. This is your initial commit.

这是管理数据库项目的新思路.当您需要更改架构时，您将进入该项目以对这些create语句进行更改，以定义您希望对象成为的状态.您始终在创建CREATE语句，而不在模式中创建ALTER语句.请查看下面的示例.

Here's the new thought process to managing your database project. As you need to make schema changes, you will come into this project to make changes to these create statements to define the state you want the object to be. You are always creating CREATE statements, never ALTER statements in your schema. Check out the example below.

更新表格 假设我们已经决定开始跟踪dbo.ETLProcess表上的更改.我们将需要用于跟踪CreatedDateTime，CreatedByID，LastUpdatedDateTime和LastUpdatedByID的列.打开dbo \ Tables文件夹中的dbo.ETLProcess文件，您将看到该表的当前版本如下:

Updating a table Let's say we've decided to start tracking changes on our dbo.ETLProcess table. We will need columns to track CreatedDateTime, CreatedByID, LastUpdatedDateTime, and LastUpdatedByID. Open the dbo.ETLProcess file in the dbo\Tables folder and you'll see the current version of the table looks like this:

CREATE TABLE [dbo].[ETLProcess] (
       [ETLProcessID] INT             IDENTITY (1, 1) NOT NULL
     , [TenantID]     INT             NOT NULL
     , [Name]         NVARCHAR (255)  NULL
     , [Description]  NVARCHAR (1000) NULL
     , [Enabled]      BIT             DEFAULT ((1)) NOT NULL
     , CONSTRAINT [PK_ETLProcess__ETLProcessID_TenantID] 
            PRIMARY KEY CLUSTERED ([ETLProcessID], [TenantID])
     , CONSTRAINT [FK_ETLProcess_Tenant__TenantID] 
            FOREIGN KEY ([TenantID]) 
            REFERENCES [dbo].[Tenant] ([TenantID])
 );

要记录我们要进行的更改，我们只需将表中的列添加如下:

To record the change we want to make, we simply add in the columns into the table like this:

CREATE TABLE [dbo].[ETLProcess] (
       [ETLProcessID]         INT             IDENTITY (1, 1) NOT NULL
     , [TenantID]             INT             NOT NULL
     , [Name]                 NVARCHAR (255)  NULL
     , [Description]          NVARCHAR (1000) NULL
     , [Enabled]              BIT             DEFAULT ((1)) NOT NULL
     , [CreatedDateTime]      DATETIME        DEFAULT(GETUTCDATE())
     , [CreatedByID]          INT
     , [LastUpdatedDateTime]  DATETIME        DEFAULT(GETUTCDATE())
     , [LastUpdatedByID]      INT
     , CONSTRAINT [PK_ETLProcess__ETLProcessID_TenantID] 
            PRIMARY KEY CLUSTERED ([ETLProcessID], [TenantID])
     , CONSTRAINT [FK_ETLProcess_Tenant__TenantID] 
            FOREIGN KEY ([TenantID]) 
            REFERENCES [dbo].[Tenant] ([TenantID])
 );

我没有在定义中添加任何外键，但是如果要创建它们，则可以将它们添加到租户外键"下方.对文件进行更改后，将其保存.

I didn't add any foreign keys to the definition, but if you wanted to create them, you would add them below the Foreign Key to Tenant. Once you've made the changes to the file, save it.

接下来要养成的习惯是检查数据库以确保其有效.在编程世界中，您将运行测试构建以确保其可编译.在这里，我们做的事情非常相似.在主菜单中，依次单击Build-> Build Database1(我们的数据库项目的名称).

The next thing you'll want to get in the habit of is checking your database to make sure it's valid. In the programming world, you'd run a test build to make sure it compiles. Here, we do something very similar. From the main menu hit Build -> Build Database1 (the name of our database project).

将打开输出窗口，并告诉您项目是否存在任何问题.在这里，您将看到诸如外键引用尚不存在的表，create object语句中的语法错误等内容.在将更新检入源代码管理之前，您需要清理它们.您将必须对其进行修复，然后才能将所做的更改部署到开发环境中.

The output window will open and tell you if there are any problems with your project. This is where you'll see things like Foreign keys referencing tables that don't yet exist, bad syntax in your create object statements, etc. You'll want to clean these up before you check your update into source control. You'll have to fix them before you will be able to deploy your changes to your development environment.

一旦数据库项目成功构建并且已签入源代码管理，就可以进行下一个更改了.

Once your database project builds successfully and it's checked in to source control, you're ready for the next change in process.

部署更改 之前我曾告诉过您，记住所有架构语句都是CREATE语句，这一点很重要.原因如下:SSDT为您提供了两种将更改部署到目标实例的方法.他们俩都使用这些create语句将您的项目与目标进行比较.通过比较两个create语句，它可以生成ALTER语句，以使目标实例与您的项目保持最新.

Deploying Changes Earlier I told you it was important to remember all your schema statements are CREATE statements. Here's why: SSDT gives you two ways to deploy your changes to a target instance. Both of them use these create statements to compare your project against the target. By comparing two create statements it can generate ALTER statements needed to get a target instance up to date with your project.

用于部署这些更改的两个选项是T-SQL更改脚本或dacpac.根据原始帖子，听起来更改脚本将是最熟悉的.

The two options for deploying these changes are a T-SQL change script, or dacpac. Based on the original post, it sounds like the change script will be most familiar.

右键单击数据库项目，然后选择模式比较".

Right click on your database project and choose Schema Compare.

默认情况下，数据库项目将位于左侧.单击右侧的选择目标"，然后选择要升级"的数据库实例.然后，单击左上方的比较"，SSDT会将项目状态与目标数据库进行比较.

By default, your database project will be the source on the left. Click Select target on the right, and select the database instance you want to "upgrade". Then click Compare in the upper left, and SSDT will compare the state of your project with the target database.

然后，您将获得目标数据库中所有不在项目中的对象的列表(在DROP部分中)，以及项目与目标数据库之间不同的所有对象的列表(在ALTER部分中) ，以及您的项目中尚未在目标数据库中的对象列表(在ADD部分中).

You will then get a list of all the objects in your target database that are not in the project (in the DROP section), a list of all objects that are different between the project and target database (in the ALTER Section), and a list of objects that are in your project and not yet in your target database (in the ADD section).

有时，您会看到您不想进行的更改(对象名称的框的更改或默认语句周围的括号的数量.您可以取消选择这样的更改.其他时候您不会准备在目标部署中部署这些更改时，您也可以取消选择这些选项.如果您选择了update(在下面的红色框)，或者在您的更改脚本中(在下面的绿色框)添加了所有选中的项，要么在目标数据库中进行了更改. ，如果您点击了生成脚本"图标.

Sometimes you'll see changes listed that you don't want to make (changes in the Casing of your object names, or the number of parenthesis around your default statements. You can deselect changes like that. Other times you will not be ready to deploy those changes in the target deployment, you can also deselect those. All items left checked will either be changed in target database, if you choose update (red box below), or added to your change script (green box below), if you hit the "Generate Script" icon.

处理数据库项目中的查找数据 现在，我们终于可以解决您的原始问题了，如何将查找数据部署到目标数据库.在数据库项目中，可以右键单击解决方案资源管理器中的项目，然后选择添加"->新建项目".您将看到一个对话框.在左侧，单击用户脚本"，然后在右侧，选择部署后脚本".

Handling lookup data in your Database Project Now we're finally to your original question, how do I deploy lookup data to a target database. In your database project you can right click on the project in Solution Explorer and choose Add -> New Item. You'll get a dialog box. On the left, click on User Scripts, then on the right, choose Post-Deployment Script.

通过添加此类型的脚本，SSDT知道您要在任何模式更改后运行此步骤.在这里，您将输入查找值，结果将其包含在源代码管理中！

By adding a script of this type, SSDT knows you want to run this step after any schema changes. This is where you will enter your lookup values, as a result they're included in source control!

现在，这是有关这些后期部署脚本的非常重要的说明.如果您在新数据库，现有数据库中调用脚本或连续调用100次，则需要确保在此处添加的任何T-SQL都可以使用.由于这一要求，我开始将所有查找值包括在merge语句中.这样，我就可以处理插入，更新和删除.

Now here's a very important note about these post deployment scripts. You need to be sure any T-SQL you add here will work if you call the script in a new database, in an existing database, or if you called it 100 times in a row. As a result of this requirement, I've taken to including all my lookup values in merge statements. That way I can handle inserts, updates, and deletes.

在将此文件提交到源代码管理之前，请在以上所有三种情况下对其进行测试，以确保其不会失败.

Before committing this file to source control, test it in all three scenarios above to be sure it won't fail.

全部打包 从直接在目标环境中进行更改到使用SSDT和源代码控制更改，这是软件开发生命周期成熟的一大步.好消息是它使您以与持续集成/连续部署方法兼容的方式将数据库考虑为部署过程的一部分.

Wrapping it all up Moving from making changes directly in your target environments to using SSDT and source controlling your changes is a big step in the maturation of your software development life-cycle. The good news is it makes you think about your database as part of the deployment process in a way that is compatible with continuous integration/continuous deployment methods.

一旦习惯了新过程，就可以学习如何将SSDT生成的dacpac添加到部署脚本中，并在适当的时候将更改推入到部署中.

Once you get used to the new process, you can then learn how to add a dacpac generated from SSDT into your deployment scripts and have the changes pushed at just the right time in your deployment.

这也使您摆脱了SELECT INTO问题，即原来的问题.

It also frees you from your SELECT INTO problem, your original problem.

这篇关于使用“选择进入".使用Azure SQL从另一个数据库复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！