Converting Oracle upsert to AzureSQL(T-SQL) prepared statement-CodePudding

I want to migrate upsert queries from Oracle DB to AzureSQL. Below shows an Oracle prepared statement that takes values from dual and does an upsert operation on the DUMMY table.

MERGE INTO DUMMY a
USING (SELECT ? ID,
              ? NAME,
              ? SIZE from dual) b
ON (a.ID = b.ID)
WHEN MATCHED THEN
    UPDATE
    SET a.ID = b.ID,
        a.NAME = b.NAME,
        a.SIZE = b.SIZE
WHEN NOT MATCHED THEN
    INSERT(a.ID,
           a.NAME,
           a.SIZE)
    VALUES ( b.ID,
             b.NAME,
             b.SIZE)

I also asked for a migration from Oracle to Postgres earlier. This is the PostgreSQL version that I asked. I am looking for a way to convert into AzureSQL now.

CodePudding user response：

Upsert for T-SQL.

MERGE dbo.table_name AS [Target]
USING (SELECT 1 AS Id, 'name' as t_name, 1 as size) AS [Source] 
   ON [Target].Id = [Source].Id
WHEN MATCHED THEN
  UPDATE SET [Target].name = [Source].t_name, [Target].size = [Source].size
WHEN NOT MATCHED THEN
  INSERT (Id, name, size) VALUES ([Source].Id, [Source].t_name, [Source].size);

Use parameter value for preparing source table

DECLARE @id int = 1,
        @name varchar(10) = 'ABC',
        @size int = 5

MERGE dbo.table_name AS [Target]
USING (SELECT @id AS Id, @name as t_name, @size as size) AS [Source] 
   ON [Target].Id = [Source].Id
WHEN MATCHED THEN
  UPDATE SET [Target].name = [Source].t_name, [Target].size = [Source].size
WHEN NOT MATCHED THEN
  INSERT (Id, name, size) VALUES ([Source].Id, [Source].t_name, [Source].size);

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=13d32c099991dc3001fe4a8cd0b3fc77