SQL Query Help Required

Stainless_Steel

Hi guys,

Looking for advice on accomplishing a merge without using a MERGE statement, as I am pre 2008 MS SQL Server.

Live table:
Location, Date, Employee, Shift, UnitsSold

Primary key is on Location, Date, Employee and Shift.

New data incoming is in the same format. I need to INSERT a new record if the PK doesn't exist in the live, or UPDATE (increase) UnitsSold if it does exist.

I can do it for an individual new record using IF EXISTS, but I'd like a solution that uses maybe joins as I could have up to 1000 new records in one go and don't want to use cursors/loops.

Thanks in advance!

neil_hosey

what format is the new data in? Is it in a spreadsheet or temp table or something?

Stainless_Steel

neil_hosey wrote: »

what format is the new data in? Is it in a spreadsheet or temp table or something?

Temp Table

Raging_Ninja

Unless I'm not understanding you, this seems straightforward enough. Assuming you don't mind doing it separately. Something like:

update live
set live.unitssolder = temptable.unitssold
from live inner join temptable on (live.[PK] = temptable.[PK])
where live.[PK] = temptable.[PK]

insert into live (Location, Date, Employee, Shift, UnitsSold)
values (temptable.Location, temptable.Date, temptable.Employee, temptable.Shift, temptable.UnitsSold)
from live inner join temptable on (live.[PK] = temptable.[PK])
where temptable.[PK] not in (select live.[PK] from live)

where [PK] is the primary key.

FSL

You do it in three steps
Step 1 update livetable set unitssold=unitssold + (select unitssold from temptable where livetable.pk = temptable.pk)
Step 2 delete temptable where temptable.pk=livetable.pk This deletes what you have just updated
step 3 Insert livetable select * from temptable This adds what's left i.e. new records