How to Insert/Update data in table without merge statement

Question

I am now load data into cursor and with in cursor loop i can check data before insert. if data exist then i update data else i insert data.

please show me a way by which i can insert data into table without cursor. i want to select data from xml and i like to insert & update data into target table without merge statement.

Right now i am doing the job this way using merge statement but i like to know without using merge can i insert update data from xml to my table. here is my code where i have done the job by merge.

how could i remove merge statement and insert/update the same data from xml into table.

declare @TickerID VARCHAR(MAX)  
SET @TickerID='TER'  
declare @UserID VARCHAR(MAX)  
set @UserID='TDP'  
  
declare @BogyXML xml= N'<?xml version="1.0" encoding="utf-16"?>                                                  
<Root>                                                  
  <PeriodicalData>                                                  
    <Section>Consensus Model</Section>                                                  
    <LineItem>Net Sales</LineItem>                                                  
    <XFundCode>TRIN</XFundCode>                                                  
    <StandardDate>2010 FY</StandardDate>                                                  
    <StandardValue>65225</StandardValue>                                                  
  </PeriodicalData>                                                  
  <PeriodicalData>                                                  
    <Section>Consensus Model</Section>                                                  
    <LineItem>Net Sales</LineItem>                                                  
    <XFundCode>TRIN</XFundCode>                                                  
    <StandardDate>2011 FY</StandardDate>                                                  
    <StandardValue>108249</StandardValue>                                                  
  </PeriodicalData>      
  <PeriodicalData>                                                  
    <Section>Segment Details</Section>                                                  
    <LineItem>Total Sales</LineItem>                                                  
    <XFundCode>TRIN</XFundCode>                                                  
    <StandardDate>2011 FY</StandardDate>                                                  
    <StandardValue>108249</StandardValue>                                                  
  </PeriodicalData>         
</Root>'  
  
CREATE TABLE #TmpTenQKData   
(  
 Section NVARCHAR(MAX),   
 LineItem NVARCHAR(MAX),  
 XFundCode NVARCHAR(MAX),  
 StandardDate NVARCHAR(MAX),  
 StandardValue VARCHAR(MAX),  
 ActualProvidedByCompany VARCHAR(MAX),  
 TickerID VARCHAR(MAX)  
)    
  
BEGIN  
 INSERT INTO #TmpTenQKData   
 SELECT col.value('(Section/text())[1]', 'NVARCHAR(MAX)') AS Section  
 ,col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)') AS LineItem     
 ,col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)') AS XFundCode  
 ,col.value('(StandardDate/text())[1]', 'NVARCHAR(MAX)') AS StandardDate  
 ,col.value('(StandardValue/text())[1]', 'VARCHAR(MAX)') AS StandardValue  
 ,col.value('(ActualProvidedByCompany/text())[1]', 'VARCHAR(MAX)') AS ActualProvidedByCompany,  
 @TickerID AS TickerID  
 FROM @BogyXML.nodes('/Root/PeriodicalData') AS tab (col)   
END  
/*  
SELECT MAX(Section) AS Section,  
MAX(LineItem) AS LineItem,  
MAX(XFundCode) AS XFundCode,  
MAX(StandardDate) AS StandardDate,  
MAX(StandardValue) AS StandardValue,  
MAX(ActualProvidedByCompany) AS ActualProvidedByCompany  
FROM #TmpTenQKData  
GROUP BY LineItem  
*/  
  
  
BEGIN  
  
 MERGE INTO TblLineItemTemplate Trg  
 USING   
 (  
 SELECT MAX(Section) AS Section,  
 MAX(LineItem) AS LineItem,  
 MAX(XFundCode) AS XFundCode,  
 MAX(StandardDate) AS StandardDate,  
 MAX(StandardValue) AS StandardValue,  
 MAX(ActualProvidedByCompany) AS ActualProvidedByCompany  
 FROM #TmpTenQKData  
 GROUP BY LineItem  
 ) AS Src   
 ON UPPER(TRIM(Trg.LineItem)) = UPPER(TRIM(Src.LineItem)) /*AND Trg.TickerID = Src.TickerID*/  
  
 WHEN MATCHED THEN   
 UPDATE SET   
 XFundCode = Src.XFundCode,  
 Action = 'U',  
 Insertdate = GETDATE()  
 WHEN NOT MATCHED THEN   
 INSERT   
 (  
 TickerID,  
 LineItem,  
 XFundCode,  
 Action,  
 UserID,  
 Insertdate  
 )  
 VALUES   
 (  
 TRIM(@TickerID),   
 TRIM(Src.LineItem),   
 TRIM(Src.XFundCode),   
 'I', @UserID,   
 GETDATE()  
 );  
END  
--select * from #TmpTenQKData  
IF OBJECT_ID('TempDB..#TmpTenQKData') IS NOT NULL    
BEGIN    
 DROP TABLE #TmpTenQKData    
END    
  
select * from TblLineItemTemplate  
  
--delete from TblLineItemTemplate  

Thanks

Answer 1

Hi @Sudip Bhatt ,

Cursor as a backup method, when the while loop, subquery, temporary table, table variable, custom function or other methods can not achieve certain queries, you can use the cursor to achieve.
Why don't we use the merge statement？

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table