How to Cross Database Queries in Azure SQL with Same Table name and structure on both database

Question

I need Ur help regarding Join query between two different Azure SQL database with same table name and same structure. Actually I have two different database with same table name and whole fields with different data.

I would like to Transfer the data from One database to another on Azure Sql or Join the table (both the table have the same name and structure). For this I am using the "Elastic Database Query"

https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/

I am facing the error message while creating External Table “CREATE EXTERNAL TABLE [dbo].[Users]”

Error Message :- "There is already an object named 'Users' in the database."

Sample Database 1st

[Test].[dbo].[Users]

CREATE TABLE [Users] (
  [UserGuid] uniqueidentifier NOT NULL
, [FirstName] nvarchar(90)  NOT NULL
, [LastName] nvarchar(90)  NOT NULL
, [UserID] nvarchar(50)  NOT NULL
, [PasswordSalt] nvarchar(90)  NOT NULL
, [Password] nvarchar(90)  NOT NULL
, [PasswordChanged] datetime NULL
, [UserName] nvarchar(50)  NOT NULL
);

GO

Database 2nd [TestAnother].[dbo].[Users]

CREATE TABLE [Users] (
  [UserGuid] uniqueidentifier NOT NULL
, [FirstName] nvarchar(90)  NOT NULL
, [LastName] nvarchar(90)  NOT NULL
, [UserID] nvarchar(50)  NOT NULL
, [PasswordSalt] nvarchar(90)  NOT NULL
, [Password] nvarchar(90)  NOT NULL
, [PasswordChanged] datetime NULL
, [UserName] nvarchar(50)  NOT NULL
);
GO

I have written the Elastic Database Query as below :

Use TestAnother
Go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ABCmn!A4F43';

CREATE DATABASE SCOPED CREDENTIAL MyDbCredential 
WITH IDENTITY = 'TestID',
SECRET = 'ABC9mn!A4F43';

CREATE EXTERNAL DATA SOURCE Connect2Test
WITH (
TYPE=RDBMS,
LOCATION='XXXX.database.windows.net',
DATABASE_NAME='Test',
CREDENTIAL= MyDbCredential,);

CREATE EXTERNAL TABLE [dbo].[Users] (
  [UserGuid] uniqueidentifier NOT NULL
, [FirstName] nvarchar(90)  NOT NULL
, [LastName] nvarchar(90)  NOT NULL
, [UserID] nvarchar(50)  NOT NULL
, [PasswordSalt] nvarchar(90)  NOT NULL
, [Password] nvarchar(90)  NOT NULL
, [PasswordChanged] datetime NULL
, [UserName] nvarchar(50)  NOT NULL 
)
WITH ( 
        DATA_SOURCE = Connect2Test);
GO

If I remove or rename the Users table on "TestAnother" database then its work fine. But I wanna use the same Table name and structure.

How can I migrate the data from one table from One database to another table on other database and both the database table name and structure are same on Azure Sql environment.

HI Both are on the same Server. I want I have a multiple database (from different customers) on the same server and I wanna club the unique data from all and generate export on our main database finally. — skt, Oct 15 '18 at 15:17
You could possibly do this with an Elastic Database Job. Create a Target Group with each of the databases with the same dbo.Users table and execute the query against them and stored the output in another table. If each of the databases has exactly the same structure but has data for different customers then I'd also consider a Sharded approach if you can add an extra key to each table with the customerid. — Martin Cairney, Oct 15 '18 at 15:44
@skt, could you please elaborate: is this a migration task i.e. that will be executed a few times only; or is this meant to be part of some OLTP-type queries? — Vladislav, Oct 15 '18 at 18:42
What if you [only have read only permissions](https://stackoverflow.com/q/53712912/6420513)? — John Drinane, Dec 17 '18 at 17:42

score 2 · Answer 1 · answered Feb 19 '20 at 09:31

You need to create the external table with different name, and define its schema and real name in the create, bellow

CREATE EXTERNAL TABLE [dbo].[ExternalUsers] (
  [UserGuid] uniqueidentifier NOT NULL
, [FirstName] nvarchar(90)  NOT NULL
, [LastName] nvarchar(90)  NOT NULL
, [UserID] nvarchar(50)  NOT NULL
, [PasswordSalt] nvarchar(90)  NOT NULL
, [Password] nvarchar(90)  NOT NULL
, [PasswordChanged] datetime NULL
, [UserName] nvarchar(50)  NOT NULL 
)
WITH ( 
        DATA_SOURCE = Connect2Test,
SCHEMA_NAME = 'dbo',
 OBJECT_NAME = 'Users');

score 0 · Answer 2 · edited Dec 10 '18 at 06:31

You want to migrate the data from one table from One database to another table on other database and both the database table name and structure are same on Azure Sql environment, just table-to-table, am I right? Since your two databases are in the same SQL server, you do this operation with SSMS(SQL Server Management Studio).

Login your Azure sql database with SSMS, choose the “SQL Server Authentication”:

Select your databse and create a new query, I have tried to migrate the data from table MyDatabase2.dbo.Users to the table MyDatabase.dbo.Users successfully by using these code:

INSERT INTO MyDatabase.dbo.Users (
  FirstName,
  LastName,
  UserID,
  PasswordSalt,
  Password,
  PasswordChanged,
  UserName) 
SELECT 
  FirstName,
  LastName,
  UserID,
  PasswordSalt,
  Password,
  PasswordChanged,
  UserName
  FROM MyDatabase2.dbo.Users

After the data is transfered, you need to decide if need to drop or delete the table in anther database.

How to Cross Database Queries in Azure SQL with Same Table name and structure on both database

2 Answers2