Solved

SQL: Update Insert Delete

Posted on 2014-04-03
14
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 41

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 41

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding SQL Server Browser in after install is complete 8 32
SQL Select Query help 1 38
Need to trim my database size 9 24
SQL query 7 16
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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