无法在SQL Azure中进行跨数据库查询 [英] Not able to do cross database query in SQL Azure

问题描述

我在同一SQL Azure服务器上有2个数据库，并且在两个DB上都有相同的表(TB1)，现在我想从DB2的TB1中读取数据并将数据插入DB1的TB1中. 我正在使用以下查询，但出现错误.

I have 2 DB on same SQL Azure server and i have same table(TB1) on both DB, now i want to read data from TB1 of DB2 and insert data into TB1 of DB1. I am using below query but getting error.

insert into TB1 select 1,* from [DB2].dbo.TB1

错误消息

MSG 40515，第15级，状态1，第16行

Msg 40515, Level 15, State 1, Line 16

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

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

推荐答案

是的，您可以在SQL Azure上使用弹性查询功能.这是执行跨数据库查询的唯一方法.

Yes, you can use the Elastic Query Features on SQL Azure.It's the only way you can perform the cross database Queries.

这是要遵循的详细查询:

Here are the detailed Queries to follow:

在您的DB1中运行以下查询(因为您说过要从DB2中读取TB1，然后将这些数据插入到DB1中的TB2中)

Run the below Query in your DB1(Since you said like reading the TB1 from DB2 and insert those Data's into your TB2 in your DB1)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STro*ngPaSSe0rD';

CREATE DATABASE SCOPED CREDENTIAL Login
WITH IDENTITY = 'Login',
SECRET = 'STro*ngPaSSe0rD';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='DB2',
    CREDENTIAL= Login
);




CREATE EXTERNAL TABLE [dbo].[TB1]
(
    [Columns] [DataTypes] 
)
WITH (DATA_SOURCE = [RemoteReferenceData])

完成这些步骤后，您可以查询外部表(如普通表).尽管在使用外部表时有一些限制，例如您无法将数据插入 EXTERNAL TABLE(参考表)

After these step, you can Query the external table like the Normal table. Though some limitations while using the External table, like you couldn't able to Insert Data's into a EXTERNAL TABLE(Reference table)

这篇关于无法在SQL Azure中进行跨数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！