Enigma State Enigma State -4 years ago 87
SQL Question

check if not exists with different table in sql server

i have the below tables

TmpProcessClaims : TPAID, Claim Number, group number , EPPID

TmpProcessPayments : SourceID, EPPID, issuerID

ISSUER : IsuerID, sourceID, TPAID, bank account name , routing number


What i need to do is, I need to get the TPAID, group number and source ID from the above two tables(TmpProcessPayments,TmpProcessClaims)
and need to compare the same in issuer table .. if I find a match i need to get corresponding issuerId and insert into
TmpProcessPayments

if i dont find a match then i need to insert the new record into issuer table and get the insert ID(IsuerID) and insert into
TmpProcessPayments

even if any two parameters are match and third one is different we need to consider as a new issuer record

for that i have done like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_InsertIssuer]
AS
BEGIN

IF NOT EXISTS (SELECT SourceID, TPAID, GroupNumber from TmpProcessClaims
INNER JOIN TmpProcessPayments
ON TmpProcessClaims.EPPID = TmpProcessPayments.EPPID
where TmpProcessClaims.TPANumber = Issuer.TPAID AND
TmpProcessClaims.GroupNumber = Issuer.GroupNumber AND
TmpProcessPayments.SourceID = issuer.SourceID)

-- i think i am missing something here

INSERT INTO Issuer (SourceID,TPAID,BankAccountNumber,RoutingNumber,BankName,GroupNumber)
SELECT


--- BUt i am not sure how to compare the records with existing records in issuer

END


could any one pls help on this query

Many thanks in advance

getting error at Issuer.TPAID

multi part identifier Issuer.TPAID could not be bound

Answer Source

From your explanation, I think that you are looking for this :

INSERT INTO Issuer (SourceID, TPAID, BankAccountNumber, RoutingNumber, BankName, GroupNumber)

SELECT SourceID, TPAID, null, null, null, GroupNumber 
FROM TmpProcessClaims
     INNER JOIN TmpProcessPayments ON TmpProcessClaims.EPPID = TmpProcessPayments.EPPID
WHERE not exists (SELECT * 
                  FROM Issuer 
                  WHERE (Issuer.TPAID = TmpProcessClaims.TPANumber AND
                         Issuer.GroupNumber = TmpProcessClaims.GroupNumber) OR           
                        (Issuer.TPAID = TmpProcessClaims.TPANumber AND                                    
                         Issuer.SourceID = TmpProcessPayments.SourceID) OR
                        (Issuer.GroupNumber = TmpProcessClaims.GroupNumber AND
                         Issuer.SourceID = TmpProcessPayments.SourceID))

As you can see, you can provide a SELECT as the source of records to add on an INSERT command. And you can filter that SELECT to ensure that the new records doesn't already exist on the target table.

PS: I have changed the filter to check if the record already exists, as requested on a comment. Now it checks two out of the three conditions to create a new record.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download