SQL: Update Insert Delete

Hi There,
Here is the challenge:
We have a Source View and a Target Table both containg the following columns:
ID, Value1, Value2
We need the following logic:
1: If ID from Source View exists in Target Table then an evaluation should be made to check if all columns in Target Table equals the columns in Source View, if they are all equal then nothing should happen, if they are not all equal then it should update the Target Table.
2: If ID from Source View does not exist in Target Table an insert should be done to the Target Table.
3: If ID in Target Table does not exist in Source View a Delete should be made in the Target Table.

It's out of my scope to create such a sql script.
Hope you can help me.
Thanks a lot.
Regards
Rasmus
ColoplastAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should work out, in the most optimal way:
DELETE T
  FROM your_table T
 WHERE NOT EXISTS (SELECT NULL FROM your_view V WHERE V.ID = T.ID)

UPDATE T
    SET  T.Value1 = V.Value1, T.Value2 = V.Value2
  FROM your_table T
  JOIN  your_view V 
   ON T.ID = V.ID
  WHERE T.Value1 <> V.Value1 
   OR T.Value2 <> V.Value2

INSERT INTO your_table ( ID, Value1, Value2 )
  SELECT V.ID, V.Value1, V.Value2
  FROM your_view V
  WHERE NOT EXISTS (SELECT NULL FROM your_table T WHERE V.ID = T.ID)

Open in new window

just fill in your table and view name(s), and it shall work.
just a note on the UPDATE part: this code assumes that neither value1 / value2 would be NULL, if you had any NULL values in there, the code would need to be updated accordingly.

performance: if the view query is heavy, you might consider fetching the view data into a local (staging) table first...
0
 
ColoplastAuthor Commented:
Hi Guy,
Thank you for your reply.
We are fetching data from the view into a staging table, but thanks for the suggestion.
Atually we have 26 columns to evaluate, do you still think it is the best way to use the OR statement to evaluate the rows in the UPdate section?
Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
presuming that you have a primary key on the ID field on both the table and the staging table, the "lookup" will be very fast.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
Why don't you try MERGE statement?
0
 
ColoplastAuthor Commented:
Hi Sharath, please enlight me with a possible solution using MERGE.
Thanks
0
 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
ColoplastAuthor Commented:
SQL 2008R2
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
MERGE reference, where I can see something "new for me":
http://msdn.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx
the "new" part is what sql server seems to have more than oracle, which is this part:
WHEN NOT MATCHED [ BY TARGET ] ...
WHEN NOT MATCHED BY SOURCE  ...

which enables to insert/delete records accordingly.
so, let me suggest then this code
MERGE  INTO  your_table T
USING your_view V
    ON V.ID = T.ID
WHEN MATCHED AND ( T.Value1 <> V.Value1 OR T.Value2 <> V.Value2 )
        THEN UPDATE SET T.Value1 = V.Value2, T.Value2 = V.Value2
WHEN NOT MATCHED BY TARGET 
         THEN INSERT (ID, Value1, Value2) VALUES ( V.ID, V.Value1, V.Value2 ) 
WHEN NOT MATCHED BY SOURCE 
        THEN DELETE
;

Open in new window

0
 
ColoplastAuthor Commented:
Thanks I will try it out.
I think the fastest suggestion will win.
0
 
ColoplastAuthor Commented:
Hi Sharath,
I've tried with the MERGE and it seems that there is an issue if I add a new record with NULL in Value1 and Value2, the record gets created in the destination but when I afterwards change the NULL to another value it's not being MERGED into the destination.
Do you know why?
Regards
Rasmus
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it would be because of this:
WHEN MATCHED AND ( T.Value1 <> V.Value1 OR T.Value2 <> V.Value2 )

which does not handle such cases implicitly, because "null" is not working with = or <>
so, you have to write this differently, for each such column:

WHEN MATCHED
AND (   ( T.Value1 <> V.Value1  OR ( T.Value1 IS NULL AND  V.Value1 IS NOT NULL ) OR ( T.Value1 IS NOT NULL AND  V.Value1 IS NULL ) )
    OR ( T.Value2 <> V.Value2  OR ( T.Value2 IS NULL AND  V.Value2 IS NOT NULL ) OR ( T.Value2 IS NOT NULL AND  V.Value2 IS NULL ) )
      )

hope this helps
0
 
ColoplastAuthor Commented:
Thanks Sharath,
It's working as intended now.
I will try testing your suggestion with 180.00 rows.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please watch our, you have 2 different experts working on your question, Sharath and myself :)
0
 
ColoplastAuthor Commented:
Thank you Guy and Sharath,
I have tested both your suggestions and concerning performance on the 180.000 rows that I have tested this on I cannot see a difference in run times.
So we have a split.
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.