How to write a SQL Script to Insert/Update Data in table

Question

In SQL Server I have a temporary table called - massupdate and a main table called retaildata

The structure is as follows.

Table massupdate:

| retail_id     | Qty| 
| 1             | 1  | 
| 2             | 2  | 

Table retaildata:

| retail_id     | Qty| 
| 1             | 1  | 
| 2             | 2  | 

retaildata has more columns also but these 2 are same as temptable massupdate.

What basically should happen is that if retail_id in massupdate table is present in retaildata then update the qty in retaildata table with qty of massupdate table. If retail_id of massupdate doesn't exist in retaildata table, then make an insert query for retaildata table. Basically, the business has given one Excel and I am feeding those data in massupdate table and then comparing with main table retaildata.

I have written a query like this but it's not correct:

IF EXISTS (SELECT retail_id FROM retaildata WHERE retail_id in (select   retail_id from massupdate))

BEGIN

    UPDATE S SET s.Qty= C.Qty FROM retaildata S inner join massupdate C on S.retail_id=C.retail_id

END
ELSE
BEGIN

    INSERT INTO retaildata (retail_id,Qty,Trigger_Suppress,Next_Ship_Date,Next_Ship_Qty,Last_Ship_Date,Last_Ship_Qty,Carrier_Text,Ship_Number,Work_Pack_Code,Supplier_Part_Number,Supplier_Inv_Qty,Ship_Comments,Last_Updt_User,Last_Updt_Date)

    SELECT     retail_id,Qty,'N',null,null,null,null,null,null,null,null,null,null,'BATCH TRRIGER INSERT/UPDATE',Getdate() FROM massupdate END

Answer 1

These types of situations are usually handled by a merge statement. Something like this should get you started

create table #retaildata (retail_id int,Qty int,Trigger_Suppress nvarchar(max),Next_Ship_Date datetime,Next_Ship_Qty datetime,Last_Ship_Date datetime,Last_Ship_Qty datetime,Carrier_Text datetime,
                            Ship_Number int,Work_Pack_Code nvarchar(max),Supplier_Part_Number int,Supplier_Inv_Qty int, Ship_Comments nvarchar(max),Last_Updt_User nvarchar(max),Last_Updt_Date datetime);

create table #massupdate (retail_id int,Qty int);

insert #retaildata (retail_id, Qty, Trigger_Suppress, Last_Updt_User) values (1, 3, 'TEST', 'Previous insert');

insert #massupdate values (1, 5), (2, 1);

select * from #retaildata;

MERGE #retaildata as target
using
(select retail_id, Qty,'N' as Trigger_suppress,null as Next_Ship_date ,null as Next_Ship_Qty,null as Last_Ship_Date ,null as Last_Ship_Qty,null as Carrier_Text,null as Ship_Number,null as Work_Pack_Code,null as Supplier_Part_Number,
null as Supplier_Inv_Qty,null as Ship_Comments,'BATCH-TRRIGER INSERT/UPDATE' as Last_Updt_User,Getdate() as Last_Updt_Date FROM #massupdate) as source
on target.retail_id=source.retail_id
when matched then update set target.Qty=source.Qty
when not matched then insert (retail_id,Qty,Trigger_Suppress,Next_Ship_Date,Next_Ship_Qty,Last_Ship_Date,Last_Ship_Qty,Carrier_Text,Ship_Number,Work_Pack_Code,Supplier_Part_Number,Supplier_Inv_Qty,
       Ship_Comments,Last_Updt_User,Last_Updt_Date)
       values (retail_id,Qty,Trigger_Suppress,Next_Ship_Date,Next_Ship_Qty,Last_Ship_Date,Last_Ship_Qty,Carrier_Text,Ship_Number,Work_Pack_Code,Supplier_Part_Number,Supplier_Inv_Qty,
       Ship_Comments,Last_Updt_User,Last_Updt_Date);

select * from #retaildata;
drop table #massupdate, #retaildata;