Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL: Update Insert Delete

Posted on 2014-04-03
14
Medium Priority
?
399 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

609 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