SQL MERGE when the two tables are in different Azure databases?-CodePudding

I want to do what SQL MERGE...WHEN MATCHED...WHEN NOT MATCHED does, but with the source and destination tables on different Azure databases. (Typically on the same server if that helps.)

The source and destination tables are not exactly the same, that's why SQL MERGE would be perfect. (I can decide what column to match on, and which columns to use in the INSERT or UPDATE.)

If this can not be done in SQL, I could load the tables into my C# code and do the merge there (will affect performance though). Does anyone know if .NET has something similar to SQL MERGE in ADO.NET (merging DataTables).

Thanks for any help!

Edit: this image shows the tables before MERGE (all Values are int):

This image shows the MERGE statement and the resulting source and dest:

CodePudding user response：

Azure SQL database doesn't support across database operations directly, even these databases are in the same Azure SQL Server. it will throw below error.

enter image description here

To work around this Azure SQL database only support the across database query with elastic query

Example

CREATE MASTER KEY; -- create master key
GO
 
-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred1 -- credential name
WITH IDENTITY = 'username',                    -- login or contained user name
SECRET = '**********';                    -- login or contained user password
GO
 
-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE source
WITH
(
    TYPE=RDBMS,                           -- data source type
    LOCATION='server.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='database1',         -- database name
    CREDENTIAL=CrossDbCred1                -- credential used to connect to server / database  
);
GO
 
-- external table points to table in an external database with the identical structure
CREATE EXTERNAL TABLE [dbo].[source]
(
    [Id] [varchar](50),
   [value1] [int],
   [value2] [int],
   [value3] [int]
)
WITH (DATA_SOURCE = [source],  -- data source 
      SCHEMA_NAME = 'dbo',           -- external table schema
      OBJECT_NAME = 'source'       -- name of table in external database
    );
GO

Now we can test our Elastic Query to merge tables.

MERGE into destination1 B
USING source E
ON (B.Id = E.Id)
WHEN MATCHED THEN
  UPDATE SET value2 = E.value2, value3 = E.value3
WHEN NOT MATCHED THEN
  INSERT (Id,value2,value3) VALUES (Id,value2,value3);

Output

Before merging
After merging