Cross Database Insert in Azure?-CodePudding

Is it possible for me to insert some data from one database to another in Azure sql?

Let's say I have a trigger in db1 that updates some values in db2.

I read about elastic queries but it seems like they are read-only so they don't solve my problem.

CodePudding user response：

There was some previous discussion here about doing similar:

C# Azure Function trigger when SQL Database has a new row added without polling

There is also the Azure SQL Bindings for Azure Functions but they are input bindings and not triggers and they're still in preview and limited to C#, JavaScript and Python.

Azure SQL bindings for Azure Functions overview (preview)

There was a new announcement last week after MS Build however for Azure SQL Database External REST Endpoints Integration (hopefully they don't refer to it as ASDEREI) but this is currently in preview under Early Adoption Program (EAP).

Announcing the “Azure SQL Database External REST Endpoints Integration” Early Adoption Program

CodePudding user response：

There are various options for doing this; two of them are given here. :

Solution 1

Right-click the database you want to copy and select Copy -> Select 'Tasks' -> 'Generate scripts' -> 'Choose specific database objects' -> Select 'Tables' -> Select 'Save to new query window' -> Click 'Advanced' -> Set 'Types of data to script' to 'Schema and data' -> Next.

The produced query can now be run on the new database.

Solution 2

Right click on the database you wisht to copy -> 'Tasks' -> 'Export Data' -> Next -> select the database to copy the tables to -> select 'Copy data from one or more tables or views' -> Select the tables you'd want to copy -> Finish

The following code is an example for inserting values from One database table into another database table running on the same SQL Server.

insert into dbo.onedatabase.FolderStatus
(
  [FolderStatusId],
  [code],
  [title],
  [last_modified]
)

select [FolderStatusId], [code], [title], [last_modified]
from dbo.Twodatabase.f_file_stat

References: How to insert data from one Azure SQL Database into a different Azure SQL Database? SQL Azure - copy table between databases Transfer data from one database to another database