HELP with UPSERT in stored procedure

Question

Hi All,
I have a SP that loads data into a table, currently it is doing TRUNCATE/INSERT. Instead I want it to do an UPSERT, the query is a long one , I am just posting a sample. In the below code where can I do an update please help.
Thanks
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE PROC [stg3].[Process_EMPCUSTDATA] AS  
IF OBJECT_ID(N'tempdb..#empdata') IS NOT NULL  
BEGIN  
DROP TABLE #empdata  
END  
IF OBJECT_ID(N'tempdb..#custdata') IS NOT NULL  
BEGIN  
DROP TABLE #custdata  
END  
IF OBJECT_ID(N'tempdb..#totaldata') IS NOT NULL  
BEGIN  
DROP TABLE #custdata  
END  
  
SELECT *  FROM EMP  
INTO #empdata  
  
SELECT * FROM CUST  
INTO #custdata  
Select t.*,t1.*  
From #empdata t  
Join #custdata t1  
On t.custid=t1.custid  
Into #totaldata  
Truncate dbo.totaldata  
Insert into dbo.totaldata select distinct * from #totaldata  
  
  

Answer 1

You can use MERGE in one statement (pseudo tsql code) :
Merge #totaldata tgt
Using ( Select /* unique column list here */ From #empdata t Join #custdata t1
On t.custid=t1.custid ) as src on tgt.custid=src.custid
When matched then
update
.....

WHen not matched then
insert
---
When not matched by source then
delete;

You need to check the syntax and logic for your implementation.

Answer 2

Hi @BKAN
1）Notice that you had this Select Into statement SELECT * FROM EMP INTO #empdata, which is incorrect. It should be like this: SELECT * INTO #empdata FROM EMP
Refer to this article for more details: SQL SELECT INTO Statement

2) In the below code where can I do an update please help.
Try this:

SELECT * FROM EMP INTO #empdata  
SELECT * FROM CUST INTO #custdata  

Select t.*,t1.*  
From #empdata t Join #custdata t1 On t.custid=t1.custid  
Into #totaldata  

UPDATE T   
SET T.Column1 = U.Column1,T.Column2 = U.Column2  
FROM dbo.totaldata T JOIN (select distinct * from #totaldata) AS U ON T.custid = U.custid;  

INSERT INTO dbo.totaldata   
SELECT U.*  
FROM (select distinct * from #totaldata) AS U   
WHERE NOT EXISTS (SELECT 1 FROM dbo.totaldata T WHERE T.custid = U.custid);  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.