XML Insert with Merge

ALTER

Procedure [dbo].[MSP_InsertRevenueDetails]

(

@_forapptrans

varchar(max)

)

as

Begin

DECLARE

@MyXML XML

SET

@MyXML =@_forapptrans

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].#Tmp') AND type in (N'U'))

DROP TABLE [dbo].#Tmp

SELECT

B

.test.query('UploadId').value('.', 'int') as UploadId,

B

.test.query('Modalityid').value('.', 'int') as Modalityid,

B

.test.query('CenterId').value('.', 'int') as CenterId ,

B

.test.query('GrossAmount').value('.', 'decimal') as GrossAmount,

B

.test.query('NoofStudy').value('.', 'int') as NoofStudy,

B

.test.query('Collection').value('.', 'decimal') [Collection],

B

.test.query('RevenueDate').value ('.','datetime') RevenueDate,

B

.test.query('CreatedBy').value ('.','varchar(20)') CreatedBy

into

#tmp

FROM

@MyXML.nodes('BulkUpload/Revenue') AS B (test)

MERGE

tbl_InsertRevenueDetails as TARGET

USING

(

SELECT

UploadId

,

Modalityid

,

CenterId

,

GrossAmount

,

NoofStudy

,

[Collection]

,

RevenueDate

,

CreatedBy

FROM

#tmp

)

as New

ON (TARGET.Modalityid=new.Modalityid and Target.CenterId = New.CenterId and Target.RevenueDate=New.RevenueDate )

WHEN MATCHED

AND

( TARGET.UploadId<>new.UploadId

OR TARGET.GrossAmount<>new.GrossAmount

OR TARGET.NoofStudy<>new.NoofStudy

OR TARGET.[Collection]<>new.[Collection]

--OR TARGET.RevenueDate<>new.RevenueDate

OR TARGET.CreatedBy<>new.CreatedBy

)

THEN

UPDATE SET

TARGET.UploadId=new.UploadId,

TARGET.GrossAmount=new.GrossAmount,

TARGET.NoofStudy=new.NoofStudy,

TARGET.[Collection]=new.[Collection],

-- TARGET.RevenueDate=new.RevenueDate,

TARGET.CreatedBy=new.CreatedBy

WHEN NOT MATCHED BY TARGET

THEN INSERT

(

[UploadId]

,[Modalityid]

,[CenterId]

,[GrossAmount]

,[NoofStudy]

,[Collection]

,[RevenueDate]

,[CreatedBy]

,[CreatedDate]

,[Status]

)

Values

(

new.UploadId,

new

.Modalityid,

new

.CenterId,

new

.GrossAmount,

new

.NoofStudy,

new

.[Collection],

new

.RevenueDate,

new

.CreatedBy,

GETDATE(),

0

);

end