SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Better query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 02/15/2013 :  16:40:31  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/16/2013 :  02:34:27  Show Profile  Reply with Quote
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

109 Posts

Posted - 02/18/2013 :  10:18:48  Show Profile  Reply with Quote
Thank you Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2013 :  10:23:01  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000