主页 / dba / 问题 / 177888
Accepted
pmdci
Asked: 2017-07-04 10:09:08 +0800 CST

在具有复合主键的表中进行一些更改的重复行，但前提是目标不存在

  • 772

考虑下表：

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 中插入行，其中具有相同主键（period AND genusId AND subjectId AND waitingStageId）的行不存在？

sql-server insert

1 个回答

  1. Best Answer

    从字面上理解您的查询所说的内容，您不必担心其中存在的内容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。因此，如果我们在 中找到一个period为 NULL的行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
;
    • 2

相关问题