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

@larnu - Yes, you are right – Sumit Dwivedi Apr 02 '20 at 11:28 — Sumit Dwivedi, Apr 02 '20 at 11:28

Dwight Reynoldson · Accepted Answer · 2020-03-19T09:14:59.887

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;

Getting an error with this query- Incorrect syntax near the keyword 'AS'. — Sumit Dwivedi, Mar 19 '20 at 08:58
I'll create a stub of data so you have an example you can run bear with — Dwight Reynoldson, Mar 19 '20 at 08:59
I have also included your sample data to demonstrate the update and insert behaviour. — Dwight Reynoldson, Mar 19 '20 at 09:15
No problem @SumitDwivedi if you can mark my answer as accepted if it has solved your problem then I would appreciate that :) — Dwight Reynoldson, Mar 19 '20 at 10:08

score -1 · Answer 2 · answered Apr 02 '20 at 11:26

-1

We have to always use MERGE statement whenever there is an Insert/Update logic involved.

answered Apr 02 '20 at 11:26

Sumit Dwivedi

21
10

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

2 Answers2