SQL Server : UPSERT transaction not reaching insert

Question

Browsing on various examples on how to create a "good" UPSERT statement shown here, I have created the following code (I have changed the column names):

BEGIN TRANSACTION

IF EXISTS (SELECT *
           FROM Table1 WITH (UPDLOCK, SERIALIZABLE), Table2
           WHERE Table1.Data3 = Table2.data3)
BEGIN
    UPDATE Table1
    SET Table1.someColumn = Table2.someColumn,
        Table1.DateData2 = GETDATE()
    FROM Table1
    INNER JOIN Table2 ON Table1.Data3 = Table2.data3
END
ELSE
BEGIN
    INSERT INTO Table1 (DataComment, Data1, Data2, Data3, Data4, DateData1, DateData2)
        SELECT  
            'some comment', data1, data2, data3, data4, GETDATE(), GETDATE()
        FROM 
            Table2
END

COMMIT TRANSACTION

My problem is, that it never does the INSERT part. The INSERT alone works fine. The current script only does the update part.

I have an idea that the insert is only good, if it can insert the entire data it finds (because of the select query)? Otherwise it won't work. If so, how can I improve it?

I have also read about the MERGE clause and would like to avoid it.

//EDIT:

After trying out few samples found on the internet and explained here, I re-did my logic as follows:

BEGIN TRANSACTION
    BEGIN
        UPDATE table1
        SET something
        WHERE condition is met 

        UPDATE table2
        SET helpColumn = 'DONE'
        WHERE condition is met
    END
    BEGIN
        INSERT INTO table1(data)
        SELECT data
        FROM table2
        WHERE helpColumn != 'DONE'
    END
COMMIT TRANSACTION

When trying other solutions, the INSERT usually failed or ran for a long time (on a few tables, I can accept it, but not good, if you plan to migrate entire data from one database to another database).

It's probably not the best solution, I think. But for now it works, any comments?

You currently check if the EXISTS returns any row, but there's no correlation to the actual UPDATE. Why don't you want to use MERGE, this is exactly why it was implemented? — dnoeth, May 16 '16 at 12:06
your exist query will always return true when there's ***any*** row that successfully joins between `table1` and `table2` - you need to do this exists based on the key information that you're checking again (as an example) — Kritner, May 16 '16 at 12:08
As far as I have read, MERGE has its flaws and as I see it, I have much more control over data, when I make custom UPDATE, INSERTS. — Asko, May 17 '16 at 09:03

score 0 · Accepted Answer · answered May 16 '16 at 12:07

0

Instead of

if (something )
update query
else 
insert query

Structure your logic like this:

update yourTable
etc
where some condition is met

insert into yourTable
etc
select etc
where some condition is met.

answered May 16 '16 at 12:07

Dan Bracuk

20,699
4
26
43

Seems dangerous - potentially, one given row that meets the condition will *first* be updated, and then in a second step *inserted*, too?!?!?!? Doesn't seem like what the OP is looking for..... – marc_s May 16 '16 at 12:21
The two conditions don't have to be the same. – Dan Bracuk May 16 '16 at 12:50
I re-did my logic with something similar (you can see it on the original post). I tried to implement INSERT where conditions were controlled over few tables, making the INSERT very slow (few seconds to many minutes slow). So to fix that, I also UPDATE my temp data table rows with a status for INSERT to avoid. Not sure, if it is the best approach tho. – Asko May 17 '16 at 09:27

score 0 · Answer 2 · answered May 17 '16 at 04:50

You cannot check this in general, like you are doing. You have to check each ID from Table 2 if it exists in Table 1 or not. If it exists, then update Table 1 else insert into Table 1. This can be done in following way.

We are going to iterate on Table 2 for each ID using CURSORS in SQL,

DECLARE @ID int
DECLARE mycursor CURSOR 
FOR
SELECT ID FROM Table2 FORWARD_ONLY  --Any Unique Column

OPEN mycursor 
FETCH NEXT FROM mycursor
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

IF EXISTS (SELECT 1 FROM Table1 WHERE ID = @ID)
  UPDATE t1 SET t1.data= T2.data  --And whatever else you want to update
  FROM
  Table1 t1
  INNER JOIN 
  Table2 t2
  ON t1.ID = t2.ID --Joining column
  WHERE t1.id = @ID
ELSE
  INSERT INTO Table1
  SELECT * FROM Table2 WHERE ID = @ID

FETCH NEXT FROM mycursor
INTO @ID
END
CLOSE mycursor
DEALLOCATE mycursor

Can the declaration @ID also be a text value? Or must it be int all the time? — Asko, May 17 '16 at 06:34
It can be of any datatype. Just declare a variable and fetch your column value into it in cursor. Both should be of same data type, that is it. — Akanksha Singh, May 17 '16 at 06:49

SQL Server : UPSERT transaction not reaching insert

2 Answers2