将Oracle SQL查询转换为Azure SQL查询 [英] Convert Oracle SQL query to Azure SQL query

问题描述

我有一个很长的Oracle SQL查询，需要与Azure SQL兼容.我对这两种数据库类型都是新手.这是查询:

I have a pretty long Oracle SQL query that needs to be compatible for Azure SQL. I am new to both database types. Here is query:

MERGE INTO studies
    USING dual
    ON (study_id = :study_id)
    WHEN NOT MATCHED THEN
        INSERT (study_id, study_date)
        VALUES (:study_id, :study_date)

我不确定使用双重是否可以使用.我读过一些解决方案，说在SQL Server中没有必要使用双重使用.

I am not sure USING dual would work. I read some solution saying that USING dual is not necessary in SQL Server.

如果您能解释该查询的含义以及如何将其翻译为Azure SQL查询，我将不胜感激.

I really appreciate if you can explain what this query means and how I can translate this for Azure SQL query.

推荐答案

此Oracle merge 查询只有 WHEN NOT MATCHED 子句，没有 WHEN MATCHED ，因此基本上是 insert 和不存在:

This Oracle merge query has just a WHEN NOT MATCHED clause and no WHEN MATCHED, so basically that's insert and not exists:

insert into studies(study_id, study_date)
select x.*
from (values(@study_id, @study_date)) as x(study_id, study_date)
where not exists (select 1 from studies s1 where s1.study_id = x.study_id)

从逻辑上讲，它等效于原始的Oracle查询.

This is logically equivalent to the original Oracle query.

对于您的原始问题:SQL Server是否支持其自身的风格或

As for your original question: SQL Server does supports its own flavor or merge statement, whose syntax is different than Oracle. You would rewrite the Oracle merge as:

merge studies as s
using (values(@study_id, @study_date)) as x(study_id, study_date)
on (s.study_id = x.study_id)
when not matched 
    then insert (study_id, study_date) values(x.study_id, x.study_date)

这篇关于将Oracle SQL查询转换为Azure SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！