SQL导入JSON数据到Directors表：如何仅插入拍过恐怖电影的导演

正确实现SQL语句 #

INSERT INTO Directors (Id, Name)
SELECT DISTINCT d.Id, d.Name
FROM OPENJSON(@DIRECTORS, '$')
     WITH (
        Id INT  '$.Id',
        Name VARCHAR(MAX) '$.Name'
     ) AS d
INNER JOIN Movie m 
    ON d.Name = m.DirectorName
WHERE m.Genre = 'Horror'

语法错误原因说明 #

之前写法的错误是把别名d写在了OPENJSON(...)后面、WITH子句前面，WITH子句是用来定义JSON解析后输出的列结构，别名必须放在WITH子句的末尾。

补充优化说明 #

• 加DISTINCT是为了避免同一个导演拍了多部恐怖片时，重复插入同一条导演数据到Directors表中，也可以替换为EXISTS写法，逻辑等价且性能更优：

INSERT INTO Directors (Id, Name)
SELECT d.Id, d.Name
FROM OPENJSON(@DIRECTORS, '$')
     WITH (
        Id INT  '$.Id',
        Name VARCHAR(MAX) '$.Name'
     ) AS d
WHERE EXISTS (
    SELECT 1 FROM Movie m 
    WHERE m.DirectorName = d.Name 
    AND m.Genre = 'Horror'
)

• 如果Directors表已有存量数据，建议额外增加主键冲突判断，避免插入报错：

INSERT INTO Directors (Id, Name)
SELECT d.Id, d.Name
FROM OPENJSON(@DIRECTORS, '$')
     WITH (
        Id INT  '$.Id',
        Name VARCHAR(MAX) '$.Name'
     ) AS d
WHERE EXISTS (
    SELECT 1 FROM Movie m 
    WHERE m.DirectorName = d.Name 
    AND m.Genre = 'Horror'
)
AND NOT EXISTS (
    SELECT 1 FROM Directors dir 
    WHERE dir.Id = d.Id
)

内容的提问来源于stack exchange，提问作者MrEmper