具有復合主鍵的表中具有少量更改的重複行，但前提是目標不存在

考慮下表：

CREATE TABLE [xxx].[MyTable](
   [period] [date] NOT NULL,
   [genusId] [int] NOT NULL,
   [subjectId] [int] NOT NULL,
   [waitingStageId] [tinyint] NOT NULL,
   [value] [tinyint] NULL,
   [savedOn] [datetime2](2) NULL,
   [savedBy] [nvarchar](50) NULL,
CONSTRAINT [PK_ExpectedActivity] PRIMARY KEY CLUSTERED 
(
   [period] ASC,
   [genusId] ASC,
   [subjectId] ASC,
   [waitingStageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 92, 1, 1, CAST(N'2017-05-29T13:45:20.6300000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 96, 1, 1, CAST(N'2017-05-29T13:45:21.2400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 0, 2, CAST(N'2017-05-29T13:45:21.6500000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 1, 3, CAST(N'2017-05-29T13:45:21.4400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 35, 0, 1, CAST(N'2017-05-26T20:05:40.2800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 0, 3, CAST(N'2017-05-26T20:05:40.6700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 1, 1, CAST(N'2017-05-26T20:05:40.4800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 23, 0, 6, CAST(N'2017-05-26T20:03:26.5400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 47, 0, 6, CAST(N'2017-05-26T20:03:26.7700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 67, 0, 3, CAST(N'2017-05-26T20:03:27.1800000' AS DateTime2), NULL)
GO

我正在嘗試創建一個儲存過程，它允許我們將上個月的值複製到目前月份——但前提是目前月份還沒有值。

DROP TABLE IF EXISTS #PreviousMonth
DECLARE @schemaName AS varchar(5) = 'xxx' -- To be used later as a parameter in stored procedure
DECLARE @lastMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE) --First day of previous month
DECLARE @currentMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) --First day of current month

-- Add rows for existing month into a temporary table
SELECT @currentMonth AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated by Stored Procedure>' AS savedBy INTO #PreviousMonth FROM [xxx].[ExpectedActivity]
 WHERE period in(@lastMonth)

SELECT * INTO xxx.MyTable FROM #PreviousMonth
-- Here is where I got stuck. I tried WHERE caluses and NOT EXISTS but my skills are limited.

不幸的是，這是我設法得到的。我的技能是有限的，它可以在不存在條件的條件下進行 INSERT。

如何確保僅在 xxx.MyTable 中插入不存在具有相同主鍵（句點 AND genusId AND subjectId AND waitingStageId）的行的行？

從字面上看您的查詢所說的內容，您不必擔心其中存在什麼xxx.myTable- 因為您的語句將創建它，因此它不會包含任何內容（或者，如果它已經存在，您的語句將失敗）。大概，這不是你真正想要的。

如果我們假設它xxx.myTable確實存在，並且可能已經有一些行的值與您的預設值匹配，那麼這應該可以工作。

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
 FROM #PreviousMonth pm
WHERE NOT EXISTS (SELECT 1
                    FROM xxx.myTable
                   WHERE period = pm.period
                     AND genusID = pm.genusID
                     AND subjectID = pm.subjectID
                     AND waitingStageID = pm.waitingStageID
                 )
;

在該NOT EXISTS子句中，您正在檢查臨時表中的目前行是否與xxx.myTable. 如果您找到目前行的匹配項，那麼您不會找到SELECT它（因此也不會INSERT）。

你也可以把它寫成所謂的“反連接”：

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
 FROM #PreviousMonth pm
        LEFT OUTER JOIN xxx.myTable mt ON (    pm.period = mt.period
                                           AND pm.genusID = mt.genusID
                                           AND pm.subjectID = mt.subjectID
                                           AND pm.waitingStageID = mt.waitingStageID
                                          )
WHERE mt.period IS NULL
;

在這裡，我們將臨時表中的所有行與xxx.myTable. 通過一個LEFT JOIN. 由於左連接，如果 中沒有匹配的行xxx.myTable，我們仍會從該表返回列，但所有值都將為 NULL。因此，如果我們找到periodNULL in的行xxx.myTable，這應該意味著我們的臨時表行沒有匹配xxx.myTable的行。因此，只有那些行（沒有匹配xxx.myTable行的臨時表行）將被SELECT編輯，因此將被INSERT編輯。

在大多數情況下，我不希望兩個版本之間的性能有顯著差異。在您的特定情況下進行測試，看看一個看起來是否比另一個更好。

更新：如果您想在關鍵欄位不存在的任何地方使用上個月的數據，或者它們確實存在但值 = 0，那麼您有幾個可用的選項。

xxx.myTable您可以刪除值為 0 的所有行，然後按上述方式進行插入。

我會傾向於UPDATE現有的行，然後執行INSERT上述操作。UPDATE聲明將是：

UPDATE mt
  SET value = pm.value
 FROM xxx.myTable mt
        INNER JOIN #PreviousMonth pm ON (    mt.period = pm.period
                                         AND mt.genusID = pm.genusID
                                         AND mt.subjectID = pm.subjectID
                                         AND mt.waitingStageID = pm.waitingStageID
                                        )
WHERE mt.value = 0
  AND pm.value <> 0
;

引用自：https://dba.stackexchange.com/questions/177888