ON DUPLICATE KEY in MS SQL

Question

In MySQL it is very easy to make dual primary keys and then we can run the

 INSERT INTO table x 
 SET
    val = x,
    val2 = y,
    val3 = z,
  ON DUPLICATE KEY SET val3 = z

Which saves time instead of doing a count and then an insert or update depending on the result.

This seems very complicated in MS SQL with it opting for this MERGE syntax.

My question is this can I make a stored procedure where I pass - the table name, fields I want to insert and then the values I would like to update.

I would then like the stored procedure to take that go away and just either do the update or the insert whichever is necessary - much like MySQL does.

I'm not very good with stored procedures on the whole and at present I'm resorting to deleting the rows and then inserting them back in as it seems to be the easiest thing to do.

I'm using PHP and ms-sql 2012

That MySQL syntax seems horrible to me. It's basically saying "if that didn't work, just make up a number and try again" — DavidG, Sep 26 '14 at 09:10
Have a look at this question, it might help you out: http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update — Bono, Sep 26 '14 at 09:20
Thanks I've read stuff like that - but I'm looking for a very generic way of getting around the problem. Like I way I'm not very good with stored procedures so if someone could write one that would be great. Perhaps the best way is to make a php function that will do the check on the primary key for you- i.e function(table,keys,values,update) that will just run the check for you? — Richard Housham, Sep 29 '14 at 07:59

score 0 · Accepted Answer · answered Oct 06 '14 at 12:20

Right well I presume there is a stored procedure method here but lack of input means that I've basically made an upsert function.

I've made it take the table, keys, values and update parameters.

public function upsert($table,$keys,$insert,$update) {

        $query = $this->from($table)->select(null)->select('COUNT(1) AS c');
        foreach($keys as $index=>$value) {
            $query->where($index,$value);
        }
        $result = $query->fetchAll();

        if($result[0]['c'] == 0) {
            //insert!
            $query = $this->insertInto($table,$insert);



            $query->execute();

        }
        else {
            $query = $this->update($table,$update);
            foreach($keys as $index=>$value) {
                $query->where($index,$value);
            }
            $query->execute();

        }

    }

This I've added into my fluentPDO object that I've been using and runs the check in the normal php ways. Might not be the best way but this works for me and using the PDO object in the middle to connect to my database gives me some control over functions like this.

Hope this is of use to someone.

ON DUPLICATE KEY in MS SQL

1 Answers1