时间戳

首页 > 解决方案 > 使插入查询幂等 - sql server

sql - 使插入查询幂等 - sql server

问题描述

任何人都知道我是如何使这个查询幂等的，即如果它被执行了不止一次，它就不会插入任何重复的记录

    INSERT INTO MyAuthDb..AspNetUserClaims ( UserId, ClaimType, ClaimValue )
SELECT c.UserId, 'givenname', c.FirstName
FROM MyOtherDb..Contact c
INNER JOIN
    (SELECT UserId, MAX(CreatedOn) AS MaxDate
    FROM MyOtherDb..Contact
    GROUP BY UserId) c1
ON c.UserId = c1.UserId 
INNER JOIN MyAuthDb..AspNetUsers u ON c.UserId = u.Id
AND c.CreatedOn = c1.MaxDate

标签： sqlsql-server

解决方案

利用NOT EXISTS.

INSERT INTO MyAuthDb..AspNetUserClaims ( UserId, ClaimType, ClaimValue )

SELECT c.UserId, 'givenname', c.FirstName
FROM MyOtherDb..Contact c
INNER JOIN
    (SELECT UserId, MAX(CreatedOn) AS MaxDate
    FROM MyOtherDb..Contact
    GROUP BY UserId) c1
ON c.UserId = c1.UserId 
INNER JOIN MyAuthDb..AspNetUsers u ON c.UserId = u.Id
AND c.CreatedOn = c1.MaxDate
WHERE NOT EXISTS (SELECT 1 FROM MyAuthDb..AspNetUserClaims A 
WHERE A.UserId = c.UserId AND A.ClaimType = 'givenname' AND A.ClaimValue = c.FirstName )

推荐阅读