UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update, Delete, Insert With One Action, SQL Server 2008 R2    
 
   
mr.siro
post Aug 29 2019, 09:57 PM
Post#1



Posts: 185
Joined: 27-January 18



Hello guys, i have Master tbl like yellow area, temp table like green area (this table is temp table of Master). Can I get result like blue area with one query (update record ID 1, delete record ID 2, insert record ID 3,4). Yes, I can do that with 3 query, but i think this not optimal.
This post has been edited by mr.siro: Aug 29 2019, 10:00 PM
Attached File(s)
Attached File  Untitled.png ( 6.02K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Aug 29 2019, 11:35 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don’t know if that’s possible. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
nvogel
post Aug 30 2019, 06:26 AM
Post#3



Posts: 1,028
Joined: 26-January 14
From: London, UK


Use MERGE to do it. Example:

MERGE INTO master USING temp
ON master.id = temp.id
WHEN MATCHED THEN UPDATE SET info = temp.info
WHEN NOT MATCHED THEN INSERT (id, info) VALUES (temp.id, temp.info)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Go to the top of the page
 
mr.siro
post Aug 30 2019, 08:19 AM
Post#4



Posts: 185
Joined: 27-January 18



great, thank nvogel. I found it, thank again.
Go to the top of the page
 
theDBguy
post Aug 30 2019, 09:44 AM
Post#5


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for this. Good info!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Aug 30 2019, 03:17 PM
Post#6


UtterAccess VIP
Posts: 1,810
Joined: 4-June 18
From: Somerset, UK


Nigel
That's an impressive SQL query...
You could do it in 2 queries in Access...Upsert (aka Upend) to do Append and Update in one query... followed by Delete.
However the performance may be worse than doing 3 separate queries.

How good will the performance be for this type of MATCH/NOT MATCHED query in SQL Server?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
nvogel
post Aug 31 2019, 07:38 AM
Post#7



Posts: 1,028
Joined: 26-January 14
From: London, UK


I have found that if MERGE is used in place of a combined UPDATE and INSERT then it generally performs about the same as those two separate DML statements. Some people claim MERGE is less efficient in some circumstances but I haven't found that a problem myself. One advantage is that MERGE avoids any risk of inconsistent results that could happen if data changes between the UPDATE and INSERT.

This post has been edited by nvogel: Aug 31 2019, 07:59 AM
Go to the top of the page
 
mr.siro
post Aug 31 2019, 09:28 AM
Post#8



Posts: 185
Joined: 27-January 18



if i use begin transaction ... commit with 3 query.
So, which is optimal ?
Go to the top of the page
 
nvogel
post Aug 31 2019, 11:15 AM
Post#9



Posts: 1,028
Joined: 26-January 14
From: London, UK


I suggest you give both methods a try and verify for yourself because a lot depends on your situation. If it's only to be done once then efficiency hardly matters. If it's a regular thing then the type and size of the updates is a factor.
Go to the top of the page
 
mr.siro
post Aug 31 2019, 11:56 AM
Post#10



Posts: 185
Joined: 27-January 18



yes, this job is run regularly. With storeprocedure and a variable table(user define table types) is source table which i have send from application. I have some test but i do not know how to find different between them. So, please tell me more detail. I need to know what is optimal, because this task is run regularly by user, i dont want to get problem with them.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 11:16 AM