SQLTeam.com | Weblogs | Forums

T-sql 2012 insert, update, delete


#1

In a t-sql 2012 database table, I need to be able to determine if a row exists in a table, update, or delete the row from the table. This will be t-sql 2012 that runs behind a .net web application. Thus would you tell me how I would do the following:

  1. If row does not exist, do an insert.
  2. If a row exists, update all columns in the selected table.
  3. If want to delete columns in a table, just want the data columns contain empty values.

#2

You can use the MERGE statement (documented here, and some interesting tips here)

Alternatively, you can use a combination of UPDATES and INSERTS. For example, if you want to insert a single row if it does not exist, and want to update it if it exists, you could do the following:

UPDATE YourTable Col1 = @Value1 WHERE SomeId = 1234;
IF @@ROWCOUNT=0
BEGIN
  INSERT INTO YourTable.....
END

#3

With all the problems they've had with MERGE, I wouldn't use it on a bet. Since it would resolve as 3 individual queries anyway, I'd use a good ol' fashioned "Upsert" consisting of 3 queries.

But, shifting gears a bit, the OP said that they also wanted to use DELETE. That very likely means that whatever the source of the updates is, it has a high probability of being a "total replacement" each time. It would be much easier and sometimes much faster to simply replace all of the data from the source. It would also guarantee that you haven't "lost sync" with the data.

Full replacement also has the benefit of being able to load table "B" while table "A" is in service and, when "B" is done loading and validated, just repoint a synonym. Next month (or whenever), do the same in reverse. The clear advantage is there will be no blocking whatsoever and the synonym repoint will auto-magically wait until no one is using it. Total "down time" will be measured in milliseconds and cause no outage.


#4

When MERGE was first introduced (or maybe when I was first introduced to MERGE), I was all excited about using it, and used it in a few queries I was working on at the time. Since then I have stopped using it, not because I ran into any issues with MERGE, but because I find it harder to read, understand and make changes. So personally I stick to the UPSERT technology these days.

I attributed my avoidance of MERGE to my lack of intellectual capacity to mentally parse the query than to any issues with MERGE. So I felt obligated to point out its existence when responding.

In a perverse way, I am glad there are problems with MERGE because now I don't have to feel so guilty about not using it :slight_smile:


#5

Heh... I have the same perversion.


#6

update Table Name set column name=@value where id=12