Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

SQL Query Help Required

  • 02-05-2014 12:31PM
    #1
    Stainless_Steel
    Closed Accounts Posts: 1,155 ✭✭✭


    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!


Comments

  • #2
    neil_hosey
    Registered Users, Registered Users 2 Posts: 1,712 ✭✭✭neil_hosey


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


  • #3
    Stainless_Steel
    Closed Accounts Posts: 1,155 ✭✭✭Stainless_Steel


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

    Temp Table


  • #4
    Raging_Ninja
    Registered Users, Registered Users 2 Posts: 1,717 ✭✭✭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.


  • #5
    FSL
    Registered Users, Registered Users 2 Posts: 1,456 ✭✭✭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


Advertisement