CODEWITHSUNDEEP
sql-serverazureazure-devopsazure-sql-database
skt
skt

Reputation: 599

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

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.

Upvotes: 3

Views: 1725

Answers (2)

Ahmed Shendy
Ahmed Shendy

Reputation: 499

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');

Upvotes: 2

Leon Yue
Leon Yue

Reputation: 16431

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”:

https://i.sstatic.net/G6h2Q.png

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.

Upvotes: 0

Related Questions