Solved

SQL: Update Insert Delete

Posted on 2014-04-03
14
382 Views
Last Modified: 2014-04-04
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
0
Comment
Question by:Coloplast
  • 7
  • 5
  • 2
14 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39974734
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
 

Author Comment

by:Coloplast
ID: 39974761
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39974781
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 40

Expert Comment

by:Sharath
ID: 39975363
Why don't you try MERGE statement?
0
 

Author Comment

by:Coloplast
ID: 39976358
Hi Sharath, please enlight me with a possible solution using MERGE.
Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39976396
What is your SQL Server version?
0
 

Author Comment

by:Coloplast
ID: 39977367
SQL 2008R2
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39977392
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
 

Author Comment

by:Coloplast
ID: 39977418
Thanks I will try it out.
I think the fastest suggestion will win.
0
 

Author Comment

by:Coloplast
ID: 39977482
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39977531
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
 

Author Comment

by:Coloplast
ID: 39977569
Thanks Sharath,
It's working as intended now.
I will try testing your suggestion with 180.00 rows.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39977635
please watch our, you have 2 different experts working on your question, Sharath and myself :)
0
 

Author Comment

by:Coloplast
ID: 39977753
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

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question