scheduled jobs in SQL Server 2012

Question

I have a question relating to scheduled jobs in SQL Server. Well, I guess it isn't exactly related to scheduled jobs, but in fact related to SQL queries.

Anyway I have 2 tables Table_1 and Table_2 in my database.

I wish to run a scheduled job every 5 minutes that would update Table_2 with all the missing records from Table_1.

For instance if Table_1 has 3 records:

1 ABC
2 PQR
3 XYZ

and Table_2 has only 2 records:

2 PQR
3 XYZ

What the job does is adds the record "1 ABC" to Table_2:

2 PQR
3 XYZ
1 ABC

the query I've written in the steps of the scheduled job is as follows:

In my code table names are different so please excuse me:

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])

Now, the problem I'm facing is that if I update/change a record in Table_1 as in if I change the ID of the record "1 ABC" to "4 ABC"

When the job runs I get the following records in Table_2

2 PQR
3 XYZ
1 ABC
4 ABC

While I'm looking for the following output:

2 PQR
3 XYZ
4 ABC

I have tried to explain my situation as well as I could. I am new to this forum, so, my apologies for asking any stupid question or not explaining it well. Any help is appreciated

EDIT:

Thank you for all the replies guys!

I believe that I have failed to mention that any column of Table_1 can be updated and the same should reflect in Table_2.

@Jibin Balachandran 's solution works fine where only UserID is updated, but not where other columns are changed.

I've come up with a solution of my own and would like your opinion:

would it make sense to delete the records from Table_2 using Right Join and then using Left Join insert the records that exist in Table_1 into Table_2?

@Ranjana Gritmire I still haven't tried your solution. Will do if nothing else works out. Thank you :)

I removed the MySQL tag since your question is about SQL Server. — Tim Biegeleisen, Dec 05 '16 at 05:06
@Sami Yes, exactly. I want the updated `ABC` and delete the old one. — J09, Dec 05 '16 at 05:13
If you can change _any_ field that means there is no key to synchronise with so you have two options: 1. simply delete and reload the table every time; 2. Create a trigger that logs changes to the table (complicated). Do the tables actually have a primary key defined? — Nick.Mc, Dec 05 '16 at 06:14
I agree with @Nick.McDermaid that you have an issue if any column can be changed you will not be able to determine when to update a record vs insert new. Unless you have unique constraints such as a name can only be used once and id can only be used once. But you can also use Change data tracking instead of having to create a trigger — Matt, Dec 05 '16 at 07:40
@Nick.McDermaid & Matt firstly, sorry about the late reply. I was caught up with work. Secondly, Nick, the tables don't _currently_ have primary keys. So based on my current situation, I have come up with a solution (coded) with the help of all answers and would like your opinion on it. Could you please look at the Answers section for the code I'm going to post and comment on that? Thank you :) — J09, Dec 05 '16 at 08:09

score 1 · Answer 1 · answered Dec 05 '16 at 05:35

You can delete the old record with the same name and different id before inserting the new one.

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])

score 1 · Answer 2 · answered Dec 05 '16 at 05:41

Try this: (It will give you idea about how to start)

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

score 1 · Answer 3 · answered Dec 05 '16 at 05:53

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
)

score 0 · Answer 4 · edited Dec 05 '16 at 07:37

Well it seems that a new row is getting INSERTED in your table rather than UPDATING the old record as the query which you have written is an INSERT statement.

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])

My assumption: I am assuming that the UserID here is ABC. If it is then ideally the record should not be inserted at all as you are using a where condition that UserID should not exist in table(in which you are going to insert).

My solution: You need to write an IF statement that check wether the ID occurs in the previous table or not.

If the ID occurs then you need to UPDATE the values with the use of an UPDATE.

If the ID does not occur then you need to INSERT the values with the use of an INSERT.

I hope I have cleared your doubt.

UserID here are the numbers. The first column. Thank you for your efforts. :) — J09, Dec 05 '16 at 05:56

J09 · Answer 5 · 2016-12-05T11:35:16.373

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])

What do you guys think about this solution?

I think this is close to if not exactly the same as Nick's 1st suggestion!

I tried this instead and got the desired output:

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])

OK, this is a fail solution since it doesn't work when there are multiple records to update. It only works when **one** record is to be updated. — J09, Dec 05 '16 at 08:55
I'm not sure about the first part but the second part assumes that `UserID` is the key. You have previously indicated this is not the case. For example if someone updates UserID from A to B, this INSERT will insert the B record as though it's a new record. Primary Keys are very important in a database. Before you go any further you should consider if you can define one now. If you can then this exercise is trivial. — Nick.Mc, Dec 05 '16 at 09:14
Besides a much simpler solution is just delete and reload the lot. Any reason you don't want to do that? — Nick.Mc, Dec 05 '16 at 09:15
No particular reason. I was only given a task with the current situation. I understand that primary keys are very important in a db. In fact, I'm guessing that in the real world implementation of this idea we will in fact be using PKs. Anyway I have found the problem with the solution that I posted. I will soon edit the answer. Thank you for your time! @Nick.McDermaid and everyone else. — J09, Dec 05 '16 at 11:34

score 0 · Answer 6 · answered Dec 05 '16 at 11:30

First you create trigger on table 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

then you can scheduled your query

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])

scheduled jobs in SQL Server 2012

6 Answers6