将Oracle upsert转换为AzureSQL(T-SQL)预备语句

Question

我想将upsert查询从Oracle DB迁移到AzureSQL。下面显示了一个Oracle准备语句，它从dual中获取值，并对DUMMY表执行upsert操作。

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)

我之前也要求从Oracle迁移到Postgres。这是我问的PostgreSQL版本。我正在寻找一种方法转换成AzureSQL现在。

Answer 1

修改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); 

使用参数值准备源表

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

请从url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=13d32c099991dc3001fe4a8cd0b3fc77查看