CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012sql-server-2014
Glory Raj
Glory Raj

Reputation: 17691

Inserting data from two temp tables into single table

I have payment table and Claim table and TmpProcessClaims table with the following columns in each table \

Claim : ClaimID pk, paymentID (FK),EPPID,Claimnumber,GroupNumber, certificate number 

Payment : paymentID (pk),EPPID, checkDate

TmpProcessClaims: TmpProcessClaimsID(pk),EPPID, ClaimNumber,Administrator,GroupNumber,

here is what i need to do... I need to take the EPPID from TmpProcessClaims and search the same EPPID in payments table and if results are there in payment table i need to insert the results into claim table from both tables payments and TmpProcessClaims 

CREATE PROCEDURE [dbo].[InsertClaims]
AS
BEGIN

 CREATE TABLE #TEMPEPPID ([EPPID] VARCHAR(150), [PaymentID] BIGINT)
 CREATE TABLE #TEMPCLAIM ([EPPID] VARCHAR(150), [GroupNumber] varchar(10),[ClaimNumber] varchar(50), [CertificateNumber] varchar(15))

 SELECT EPPID , PaymentID  
 INTO #TEMPEPPID
 FROM DBO.Payment

 SELECT EPPID, [GroupNumber],[ClaimNumber],[CertificateNumber]
 INTO #TEMPCLAIM
 FROM [dbo].[TmpProcessClaimsToMedPay] 
 where EPPID in (select EPPID from #TEMPEPPID)

 INSERT INTO Claim ....

END 
GO

But i am not sure how to insert the data from two temp table into single table is this is correct way to proceed or any other ways to go through this criteria

could any one pls help on this issue ..

many thanks in advance ...

Upvotes: 0

Views: 522

Answers (1)

SAS
SAS

Reputation: 4035

Just join the tables and insert:

INSERT INTO Claim([column names])
SELECT [column names]
FROM DBO.Payment AS p
INNER JOIN [dbo].[TmpProcessClaimsToMedPay] AS c
 where p.EPPID = c.EPPID

Upvotes: 1

Related Questions