エーティーエルシステムズ 鍋島です。
今回は、Azure SQLデータベースで同一サーバー上のデータベース間でSELECT INTO する方法について紹介します。
SQL Serverでは問題なく別データベースのテーブルを参照出来てたので当然出来ると思い込んでいたのですが、Azureではできないことがわかりました。
- コピー元：TESTDB01のテーブルTable01
- コピー先：TESTDB02のテーブルTable01
SELECT INTOでテーブルのコピーしようとしたんですが、
SELECT * INTO dbo.Table01
FROM testdb01.dbo.Table01
エラーが出力されます。なんでー！（エラーメッセージを読みましょう。）
メッセージ 40515、レベル 15、状態 1、行 16
Reference to database and/or server name in 'testdb01.dbo.Test01' is not supported in this version of SQL Server.
どうやら、別DBのテーブルを参照できないようです。
SELECT * FROM testdb01.dbo.Test01
メッセージ 40515、レベル 15、状態 1、行 16
Reference to database and/or server name in 'testdb01.dbo.Test01' is not supported in this version of SQL Server.
そこで調べたところ、別データベースのテーブルを参照できる方法がありましたので、その手順を説明します。
#別データベースのテーブルを参照する手順
次の4ステップで参照できるようになります。
- マスターキーを作成
- 資格情報を作成
- 外部リソースを作成
- 外部テーブルを作成
以下、手順の詳細です。
マスターキーを作成
マスターキーを作成していな場合は作成します。
このキーは、パスワードを使用して暗号化されます。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ここにマスターキーをコピーするためのパスワード';
資格情報を作成
資格情報をTESTDB01USERという名前で作成します。
CREATE DATABASE SCOPED CREDENTIAL TESTDB01USER WITH IDENTITY='ここにSQLユーザー名', SECRET = 'ここにSQLユーザーのパスワード';
--作成したクレデンシャルを表示して確認
SELECT * FROM sys.database_scoped_credentials;
外部リソースを作成
データベースを外部リソースとして参照します。
作成した資格情報 TESTDB01USER を使ってtestdb01という名前で外部リソースを作成し、
データベースを参照できるようにします。
CREATE EXTERNAL DATA SOURCE testdb01 WITH
(
TYPE = RDBMS,
LOCATION = N'{ここにサーバー名}.database.windows.net', -- サーバ
DATABASE_NAME = N'testdb01', -- データベース名
CREDENTIAL = TESTDB01USER --作成した
)
外部テーブルを作成
外部テーブルの作成し、データベースから参照できるようにします。
外部テーブルと通常のテーブルの名前を同じにすることはできません。
今回はSELECT * INTO したいので、同じ名前のテーブルがあると都合が悪いので名前を変えます。
ここでは、ExternalTable01という名前に変えます。
CREATE EXTERNAL TABLE dbo.ExternalTable01
(
Id int Not NULL,
Value nvarchar(MAX) NULL
)
WITH
(
DATA_SOURCE = testdb01,
SCHEMA_NAME='dbo', --スキーマ
OBJECT_NAME='Table01' --元々のテーブル名
)
GO
これでテーブルが参照できるようになったので、無事SELECT INTOできるようになりました！
SELECT * INTO dbo.Table01
FROM ExternalTable01
まとめ
外部テーブルを作成すると、他のデータベースのテーブルを参照できるようになって、SELECT * INTO もできるようになります！
（が、ちょっと面倒なのでサポートしてほしいな。）
