我有一个查询,但它给了我一个 无法在对象中插入重复键错误。我知道我需要包含一个不存在的语句,但无论我在哪里尝试,我都会出错。知道它会去哪里吗?
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevel
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c
假设主键是(ProductID, CustomerLevel)
你可以尝试类似的东西
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c
WHERE NOT EXISTS (
SELECT 1
FROM ProductCustomerLevel pcl
WHERE p.ProductID = pcl.ProductID
AND c.CustomerLevel = pcl.CustomerLevel
)
只需在运行插入之前执行以下命令:
DELETE FROM ProductCustomerLevel
WHERE CustomerLevel IN (5, 7)