Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Better query

Author  Topic 

rama108
Posting Yak Master

115 Posts

Posted - 2013-02-15 : 16:40:31
How can I write this in a better way?

INSERT INTO HFC.dbo.ERRORS (TABLENAME, ROW_ID, MPI, ERRORMSG)
SELECT @tablename AS TableName,
ROW_ID,
MPI,
'No Matching record' AS ErrorMSG
FROM @t sp
WHERE NOT EXISTS
(SELECT 1
FROM @t sps JOIN eli.dbo.Elig e ON e.EID = sps.E_ID
JOIN eli.dbo.Accounts ea ON ea.AccountID = e.ACCOUNTID
JOIN LFL.dbo.Accounts la ON la.AcctID = ea.Acctid
JOIN LFL.dbo.pa p ON p.AccountID = la.ACCOUNTID
AND p.SSNum = e.HCNID
WHERE sps.ROW_ID = sp.ROW_ID
AND sps.EID = sp.EID
AND sps.MPI = sp.MPI
);

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-16 : 02:34:27
why do you need @t twice? wont this suffice?


INSERT INTO HFC.dbo.ERRORS (TABLENAME, ROW_ID, MPI, ERRORMSG)
SELECT @tablename AS TableName,
ROW_ID,
MPI,
'No Matching record' AS ErrorMSG
FROM @t sp
WHERE NOT EXISTS
(SELECT 1
FROM eli.dbo.Elig e
JOIN eli.dbo.Accounts ea ON ea.AccountID = e.ACCOUNTID
JOIN LFL.dbo.Accounts la ON la.AcctID = ea.Acctid
JOIN LFL.dbo.pa p ON p.AccountID = la.ACCOUNTID
AND p.SSNum = e.HCNID
WHERE e.EID = sp.E_ID
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rama108
Posting Yak Master

115 Posts

Posted - 2013-02-18 : 10:18:48
Thank you Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:23:01
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -