Home Using temp table check records exist or not – if present then do the same operation

Questions

Using temp table check records exist or not – if present then do the same operation

August 14, 2023

If records are present or not present, then those records should be inserted into temp table along with status. I am looking for very high performance query, because this SP is being called online with many users. Please help me.

I have a temp table and one master table, and I need to compare with master table, if the records present then status should be "Valid", if not present, then status needs to be "Invalid".

MainRecords: in this table CorrectNumber column might be repeated with different dates as mentioned below:

I am looking for final output should be like below :

#TempReferencIds:

'8273648','3333333',’INVALID’
'3434448','1111111', ’INVALID’
'2244448','2222222', ’INVALID’
'10101010','7003594558',’VALID’
'20202020','7003310682',’VALID’
'30303030','6130985705',’VALID’

This is what I have tried – but it is not showing the correct result which I am expecting as mentioned above table :

CREATE TABLE MainRecords
(
        CorrectNumber nvarchar(20),
        name nvarchar(20),
        Date nvarchar(20)
)

INSERT INTO MainRecords VALUES ('7003594558', 'sssss', GETDATE())
INSERT INTO MainRecords VALUES ('7003310682', 'gfdga', GETDATE())
INSERT INTO MainRecords VALUES ('7003310682', 'gfdga', GETDATE()- 17)
INSERT INTO MainRecords VALUES ('6130985705', 'fdgfd', GETDATE())
INSERT INTO MainRecords VALUES ('6130985705', 'fdgfd', GETDATE()-1)
INSERT INTO MainRecords VALUES ('6130985705', 'fdgfd', GETDATE()-3)

CREATE TABLE #InputRecords
(
    InCorrectNumber varchar(20),
    CorrectNumber nvarchar(20)
)

INSERT INTO #InputRecords VALUES ('8273648', '3333333')
INSERT INTO #InputRecords VALUES ('3434448', '1111111')
INSERT INTO #InputRecords VALUES ('2244448', '2222222')
INSERT INTO #InputRecords VALUES ('10101010', '7003594558')
INSERT INTO #InputRecords VALUES ('20202020', '7003310682')
INSERT INTO #InputRecords VALUES ('30303030', '6130985705')

CREATE TABLE #TempReferencIds
(
    InCorrectNumber varchar(20),
    CorrectNumber nvarchar(20),
    Status nvarchar(10),
)

IF (EXISTS(SELECT 1 
           FROM [dbo].MainRecords a 
           JOIN #InputRecords b ON a.CorrectNumber = b.CorrectNumber)) 
BEGIN
    INSERT INTO #TempReferencIds (InCorrectNumber, CorrectNumber, Status)
        SELECT t.InCorrectNumber, t.CorrectNumber, 'VALID' 
        FROM [dbo].MainRecords a 
        JOIN #InputRecords t ON a.CorrectNumber = t.CorrectNumber
 END
 ELSE
 BEGIN
     INSERT INTO #TempReferencIds (InCorrectNumber, CorrectNumber, Status)
         SELECT t.InCorrectNumber, t.CorrectNumber, 'INVALID' 
         FROM [dbo].MainRecords a 
         JOIN #InputRecords t ON a.CorrectNumber <> t.CorrectNumber
END

SELECT * FROM #TempReferencIds

>Solution :

Try the query below

INSERT INTO #TempReferencIds (InCorrectNumber, CorrectNumber, Status)
SELECT t.InCorrectNumber, t.CorrectNumber, 'VALID' 
FROM [dbo].MainRecords a 
JOIN #InputRecords t ON a.CorrectNumber = t.CorrectNumber
UNION
SELECT t.InCorrectNumber, t.CorrectNumber, 'INVALID' 
FROM [dbo].MainRecords a 
RIGHT JOIN #InputRecords t ON a.CorrectNumber = t.CorrectNumber
WHERE a.CorrectNumber IS NULL