Sql 使用临时表判断记录是否存在 如果存在则执行相同的操作

如果存在或不存在记录，则应将这些记录与状态一起插入到临时表中.I am looking for very high performance query, because this SP is being called online with many users. Please help me.个

我有一个临时表和一个主表，我需要与主表进行比较，如果存在记录，则状态应为"有效"，如果不存在，则状态需要为"无效".

MainRecords:在此表中，CorrectNumber列可能会以不同的日期重复，如下所述:

我正在寻找的最终输出应如下所示:

#TempReferencIds:

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

这就是我已经try 过的--但它没有显示出我所期望的正确结果，如上表所述:

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