Solved

SQL: Update Insert Delete

Posted on 2014-04-03
14
380 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 142

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 142

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

 
LVL 142

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 142

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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now