将Oracle upsert转换为AzureSQL(T-SQL)预准备语句
将Oracle upsert转换为AzureSQL(T-SQL)预准备语句
提问于 2021-09-16 06:34:17
我想将upsert查询从Oracle DB迁移到AzureSQL。下面显示了一条Oracle预准备语句，该语句从dual中获取值，并在虚拟表上执行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版本就是This。我现在正在寻找一种转换成AzureSQL的方法。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-09-16 07:16:27
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); 使用参数值准备源表
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查看
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69203390
复制相关文章
相似问题
腾讯云开发者
