How to migrate Azure SQL Database to another Azure SQL Database

Question

I have one SQLServer in Azure portal and in that server has 2 SQL Databases TestDB1 and TestDB2 is copy of TestDB1. But we used TestDB2 for testing and now it has more data compared to TestDB1. I want to migrate only unavailable data from TestDB2 to TestDB1 as both are having same DB schema. How to do it?

Answer 1

Something like this might work, I've tested it locally and it does merge in test data from a sperate database, full example shown

USE [Playground2] -- Swap for your database name

CREATE TABLE MyTable (
    Id BIGINT NOT NULL IDENTITY(1,1) CONSTRAINT PK_MyTable PRIMARY KEY, 
    [Name] NVARCHAR(50), 
    [Age] INT, 

)

INSERT INTO MyTable([Name], [Age])
VALUES('Andrew', '28'),
('Robert', '38'),
('James', '40'),
('Robin', '40'), 
('Peter', '56'), -- second database has this extra data
('Steve', '22') -- second database has this extra data

GO

USE [Playground] -- Swap for your database name

CREATE TABLE MyTable (
    Id BIGINT NOT NULL IDENTITY(1,1) CONSTRAINT PK_MyTable PRIMARY KEY, 
    [Name] NVARCHAR(50), 
    [Age] INT, 

)

INSERT INTO MyTable([Name], [Age])
VALUES('Andrew', '28'),
('Robert', '38'),
('James', '40'),
('Robin', '40') 

GO

-- Check that the tables have slightly different data
SELECT * FROM Playground.dbo.MyTable
SELECT * FROM Playground2.dbo.MyTable

BEGIN TRANSACTION
BEGIN TRY

    SET IDENTITY_INSERT dbo.MyTable ON

    MERGE INTO dbo.MyTable AS TGT
    USING [Playground2].dbo.MyTable AS SRC -- Note that we point to the other database here seeing as it is on the same SQL instance
        ON  TGT.Id = SRC.Id
    WHEN MATCHED THEN
        UPDATE SET 
        TGT.[Name] = SRC.[Name],
        TGT.[Age] = SRC.[Age]
    WHEN NOT MATCHED THEN
        INSERT(Id, [Name], [Age])
        VALUES(SRC.Id, SRC.[Name], SRC.[Age])
    OUTPUT $action AS [Action],
        deleted.[Name] AS OldName, 
        inserted.[Name] AS [NewName],
        deleted.[Age] AS OldCountry, 
        inserted.[Age] AS NewCountry;

    SET IDENTITY_INSERT dbo.MyTable OFF

    SELECT * FROM dbo.MyTable
        
    ROLLBACK TRANSACTION -- Change to COMMIT TRANSACTION when you are happy with  the results

END TRY
BEGIN CATCH
    PRINT 'Rolling back changes, there was an error!!' 
    ROLLBACK TRANSACTION
    DECLARE @Msg NVARCHAR(MAX)  
    SELECT @Msg=ERROR_MESSAGE() 
    RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
END CATCH

But there also will be tools to do this, but this could be one answer, cheers