scheduled jobs in SQL Server 2012
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 :)
在插入新记录之前，您可以删除具有相同名称和不同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])
试试这个:(它会让你知道如何开始）
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
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
)