SQL Server 2012中的计划任务

Question

我有一个与SQL Server中的定时作业相关的问题。嗯，我猜这不完全与定时作业有关，而是与SQL查询有关。

无论如何，我在我的数据库中有2个表Table_1和Table_2。

我希望每5分钟运行一个定时作业，更新Table_2中所有缺失的记录，使其与Table_1一致。

例如，如果Table_1有3条记录：

1 ABC
2 PQR
3 XYZ

Table_2只有2条记录：

2 PQR
3 XYZ

该工作的作用是向Table_2添加记录“1 ABC”：

2 PQR
3 XYZ
1 ABC

我在定时任务的步骤中编写的查询如下：

由于我的代码表名不同，请见谅：

Table_1 = [sfs_test].dbo.[Table_1],
Table_2 = [sfs_test2].dbo.[Table_1]

INSERT INTO [sfs_test2].dbo.[Table_1] (UserID, UserName)
SELECT UserID, UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_1])

现在，我面临的问题是，如果我更新/更改Table_1中的一条记录，例如将记录“1 ABC”的ID更改为“4 ABC”

当作业运行时，我会在Table_2中得到以下记录：

2 PQR
3 XYZ
1 ABC
4 ABC

我正在寻找以下输出：

2 PQR
3 XYZ
4 ABC

我已经尽力解释我的情况了。我是这个论坛的新手，所以如果问了一些愚蠢的问题或者没有解释清楚，我深表歉意。 非常感谢任何帮助。

编辑：

谢谢大家的回复！

我相信我没能提到的是，Table_1的任何一列都可以更新，而这些更新应该反映在Table_2中。

@Jibin Balachandran 的解决方案可以在仅更新 UserID 时有效，但在更改其他列时无效。

我已经想出了自己的解决方案，并想听听你们的意见：

使用 Right Join 删除 Table_2 中的记录，然后使用 Left Join 将 Table_1 中存在的记录插入到 Table_2 中是否合理？

@Ranjana Gritmire 我还没有尝试过您的解决方案。如果没有其他办法，我会试一试。谢谢:)

- J09

---

我删除了MySQL标签，因为你的问题是关于SQL Server的。 - Tim Biegeleisen

谢谢大家的所有编辑！看起来干净多了 :) - J09

@Sami 是的，没错。我想要更新后的 ABC 并删除旧的。 - J09

如果您可以更改“任何”字段，这意味着没有要同步的键，因此您有两个选择：1.每次仅删除并重新加载表；2.创建一个记录表更改的触发器（复杂）。这些表是否实际上定义了主键？ - Nick.McDermaid

我同意@Nick.McDermaid的观点，如果任何列都可以更改，那么你将无法确定何时更新记录还是插入新记录。除非你有唯一约束条件，例如名称只能使用一次和ID只能使用一次。但是你也可以使用变更数据跟踪，而不必创建触发器。 - Matt

Answer 1

在插入新记录之前，您可以删除名称相同但 ID 不同的旧记录。

DELETE [sfs_test2].dbo.[Table_1] 
WHERE EXISTS (
               SELECT 1 
               FROM [sfs_test].dbo.[Table_1]
               WHERE [sfs_test].dbo.[Table_1].UserName=[sfs_test2].dbo.[Table_1]. UserName
               AND [sfs_test].dbo.[Table_1].UserID<>[sfs_test2].dbo.[Table_1].UserID )


INSERT INTO [sfs_test2].dbo.[Table_1] (UserID, UserName)
SELECT UserID, UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_1])

- Jibin Balachandran

Answer 2

试试这个：（它将帮助你了解如何开始）

IF EXISTS(SELECT * FROM TABLE_1 WHERE ID NOT IN
            (SELECT ID FROM TABLE_2) AND VAL NOT IN (SELECT VAL FROM TABLE_2))
BEGIN
INSERT INTO TABLE_2 SELECT * FROM TABLE_1 WHERE ID NOT IN (SELECT ID FROM TABLE_2)
END

IF EXISTS(SELECT * FROM TABLE_1 WHERE ID NOT IN (SELECT ID FROM TABLE_2) 
            OR VAL NOT IN (SELECT VAL FROM TABLE_2))
BEGIN
UPDATE TABLE_2 SET ID=((SELECT ID FROM TABLE_1 WHERE ID 
                        NOT IN (SELECT ID FROM TABLE_2) 
                        OR VAL NOT IN (SELECT VAL FROM TABLE_2)))
        WHERE VAL=(SELECT VAL FROM TABLE_1 WHERE ID
         NOT IN (SELECT ID FROM TABLE_2) OR VAL NOT IN (SELECT VAL FROM TABLE_2))
END


SELECT * FROM TABLE_2

- Ranjana Ghimire

Answer 3

DECLARE @TAB AS TABLE (Id int, Duplicate varchar(20))

INSERT INTO @TAB 
SELECT 1, 'ABC' UNION ALL
SELECT 2, 'ABC' UNION ALL
SELECT 3, 'LMN' UNION ALL
SELECT 4, 'XYZ' UNION ALL
SELECT 5, 'XYZ'

DELETE FROM @TAB WHERE Id IN (
SELECT Id FROM (
    SELECT 
        Id
        ,ROW_NUMBER() OVER (PARTITION BY [Duplicate] ORDER BY Id) AS [ItemNumber]
        -- Change the partition columns to include the ones that make the row distinct
    FROM 
        @TAB 
) a WHERE ItemNumber > 1 -- Keep only the first unique item
)

- Ilyes

Answer 4

首先在表1上创建触发器

CREATE TRIGGER trgAfterupdate ON [sfs_test].dbo.[Table_1]
FOR update
AS
    declare @empid int;
    declare @empname varchar(30);
    select @empid=i.UserID from inserted i; 
    select @empname=i.UserName from inserted i;
    if update(UserID)
    UPDATE [sfs_test2].dbo.[Table_1] SET UserID=@empid WHERE UserName=@empname
    if UPDATE(UserName)
    UPDATE [sfs_test2].dbo.[Table_1] SET UserName=@empname WHERE UserID=@empid
GO

然后您可以安排查询

INSERT INTO [sfs_test2].dbo.[Table_1] (UserID, UserName)
SELECT UserID, UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_1])

- ajinkya bandagale

Answer 5

好像您的表格正在插入新行而不是更新旧记录，因为您编写的查询是一个插入语句。

INSERT INTO [sfs_test2].dbo.[Table_1] (UserID, UserName)
SELECT UserID, UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_1])

我的假设：我假设这里的UserID是ABC。如果是这样，理想情况下记录根本不应该被插入，因为您正在使用一个条件，即UserID不应存在于表中（您将要插入的表）。

我的解决方案：您需要编写一个IF语句来检查ID是否出现在先前的表中。

如果ID出现，则需要使用UPDATE更新值。

如果ID不存在，则需要使用INSERT插入值。

希望我已经解决了您的疑问。

- Rishabh Toki

---

用户ID在这里是数字。第一列。感谢您的努力。 :) - J09

Answer 6

DELETE FROM sfs_test2.dbo.Table_2
    WHERE sfs_test2.dbo.Table_2.UserID = 
        (select sfs_test2.dbo.Table_2.UserID
        from sfs_test.dbo.Table_1
        right join sfs_test2.dbo.Table_2
        on sfs_test.dbo.Table_1.UserID = sfs_test2.dbo.Table_2.UserID
        and sfs_test.dbo.Table_1.UserName = sfs_test2.dbo.Table_2.UserName
        where sfs_test.dbo.Table_1.UserID IS NULL
        and sfs_test.dbo.Table_1.UserName IS NULL)
        AND sfs_test2.dbo.Table_2.UserName = 
        (select sfs_test2.dbo.Table_2.UserName
        from sfs_test.dbo.Table_1
        right join sfs_test2.dbo.Table_2
        on sfs_test.dbo.Table_1.UserID = sfs_test2.dbo.Table_2.UserID
        and sfs_test.dbo.Table_1.UserName = sfs_test2.dbo.Table_2.UserName
        where sfs_test.dbo.Table_1.UserID IS NULL
        and sfs_test.dbo.Table_1.UserName IS NULL)

INSERT INTO [sfs_test2].dbo.[Table_2] (UserID, UserName)
SELECT sfs_test.dbo.Table_1.UserID, sfs_test.dbo.Table_1.UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_2])

你们觉得这个解决方案怎么样？

我认为这个解决方案与尼克的第一个建议非常接近，如果不是完全一样的话！

我尝试了这个方法，得到了期望的输出结果：

DELETE FROM sfs_test2.dbo.Table_2
    WHERE sfs_test2.dbo.Table_2.UserID IN
        (select sfs_test2.dbo.Table_2.UserID
        from sfs_test.dbo.Table_1
        right join sfs_test2.dbo.Table_2
        on sfs_test.dbo.Table_1.UserID = sfs_test2.dbo.Table_2.UserID
        and sfs_test.dbo.Table_1.UserName = sfs_test2.dbo.Table_2.UserName
        where sfs_test.dbo.Table_1.UserID IS NULL
        and sfs_test.dbo.Table_1.UserName IS NULL)

        AND sfs_test2.dbo.Table_2.UserName IN
        (select sfs_test2.dbo.Table_2.UserName
        from sfs_test.dbo.Table_1
        right join sfs_test2.dbo.Table_2
        on sfs_test.dbo.Table_1.UserID = sfs_test2.dbo.Table_2.UserID
        and sfs_test.dbo.Table_1.UserName = sfs_test2.dbo.Table_2.UserName
        where sfs_test.dbo.Table_1.UserID IS NULL
        and sfs_test.dbo.Table_1.UserName IS NULL)

INSERT INTO [sfs_test2].dbo.[Table_2] (UserID, UserName)
SELECT sfs_test.dbo.Table_1.UserID, sfs_test.dbo.Table_1.UserName
FROM [sfs_test].dbo.[Table_1]
WHERE UserID NOT IN (SELECT DISTINCT UserID 
                         FROM [sfs_test2].dbo.[Table_2])

- J09

---

好的，这是一个失败的解决方案，因为当有多条记录需要更新时它无法工作。它只能在更新一条记录时起作用。 - J09

我不确定第一部分，但第二部分假设UserID是关键字。您之前已经表明这不是事实。例如，如果有人将UserID从A更新为B，则此INSERT将插入B记录，就像它是一个新记录一样。主键在数据库中非常重要。在继续之前，您应该考虑是否现在可以定义一个主键。如果可以，那么这个练习就很简单了。 - Nick.McDermaid

除此之外，一个更简单的解决方案就是删除并重新加载所有内容。您不想这样做的原因是什么？ - Nick.McDermaid

没有特别的原因。我只是在当前情况下被分配了一个任务。我知道主键在数据库中非常重要。实际上，我猜想在这个想法的实际实现中，我们将会使用主键。无论如何，我已经找到了我发布的解决方案的问题。我很快就会编辑答案。谢谢你们的时间！@Nick.McDermaid和其他所有人。 - J09