要在数据库中更新的数据（如果存在）-否则应在SQL Server中插入新记录

debugcn 发表于 Dev

Murli

我创建了一个插入语句，以在数据不存在时将数据添加到数据库。

INSERT INTO SampleList(SampleId, Type, Description)
    SELECT nr.SampleId, nr.Type, nr.Description
      FROM (VALUES (1,'A','AA'),
                 (2,'B','BB'),
                 (3,'C','CC'),
                 (4,'D','DD')
           ) AS nr (SampleId,Type,Description) 
      LEFT JOIN SampleList cr ON cr.SampleId = nr.SampleId 
      WHERE cr.SampleId IS NULL

但是，如果数据库中存在数据，我也想更新行。

你能帮我吗？

Yogesh Sharma

您可以使用MERGE：

MERGE SampleList cr 
USING (SELECT nr.SampleId, nr.[Type], nr.[Description] 
       FROM (VALUES (1,'A','AA'), (2,'B','BB'), (3,'C','CC'), (4,'D','DD') 
                  ) as nr (SampleId, [Type], [Description])
      ) AS nr -- Use missed alias
       ON (cr.SampleId = a.SampleId) 
WHEN MATCHED THEN 
     UPDATE SET cr.[Type] = nr.[Type], cr.[Description] = nr.[Description] 
WHEN NOT MATCHED BY TARGET THEN 
     INSERT (SampleId, [Type], [Description]) 
        VALUES (nr.SampleId, nr.[Type], nr.[Description]); -- to terminate the merge

但是，您可以仅使用来简化子查询values：

MERGE SampleList cr 
USING ( VALUES (1,'A','AA'), (2,'B','BB'), (3,'C','CC'), (4,'D','DD') 
      ) AS nr(SampleId, [Type], [Description]) 
      ON (cr.SampleId = a.SampleId)
WHEN MATCHED THEN 
     UPDATE SET cr.[Type] = nr.[Type], cr.[Description] = nr.[Description] 
WHEN NOT MATCHED BY TARGET THEN 
     INSERT (SampleId, [Type], [Description]) 
        VALUES (nr.SampleId, nr.[Type], nr.[Description]); -- to terminate the merge

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。