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

Answer 1

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

Answer 2

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