June 21, 2012 at 12:43 pm
I am doing some research for a lunch and learn at my work and one of the topics I will be presenting is the MERGE Statement. In doing some research, I keep reading that a "MERGE Statement will perform better than the Traditional Ways". My question is what is considered the "Traditional Ways"? Is this referring to a IF THEN ELSE or a Cursor or using Temp tables to do some DML?
If someone can clarify for me "Traditional Ways" above or maybe a better question would be, What is the TSQL Alternative to a MERGE Statement, It would be greatly appreciated.
Thanks
June 21, 2012 at 12:46 pm
MERGE seems to be a theme lately. For ease, check out this thread:
http://www.sqlservercentral.com/Forums/FindPost1316908.aspx
If you have further questions after that about it, please, feel free to keep posting either in this thread or to continue the conversation in that one. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2012 at 1:08 pm
The "traditional way" was to do an UPDATE then an INSERT, each time doing a join from the new data to the existing table data to verify that they key does / does not already exist, respectively.
For example, say i have TableA that contains keys 1, 4, 7 and 10.
I have new data, in table #data, and I want to UPDATE or INSERT keys 1, 2, 5 and 10 -- if the key matches, update, if not, insert.
Traditionally one would have to do as below, before MERGE existed:
UPDATE a
SET
a.data1 = d.data1,
a.data2 = d.data2,
...
FROM #data d
INNER JOIN dbo.TableA a ON
a.key = d.key
INSERT INTO a (key, data1, data2, ...)
SELECT
d.key, d.data1, d.data2, ...
FROM #data d
LEFT OUTER JOIN dbo.TableA a ON
a.key = d.key
WHERE
a.key IS NULL
Now, instead, a single MERGE statement can do both the UPDATEs and the INSERTs, as required.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 22, 2012 at 12:18 am
Another traditional way is doing it in SSIS: OLE DB Destination with Fast Load for the inserts and OLE DB Command (ugh) for the updates.
This was slow because the updates would have been done row by row. You can solve this issue by inserting the rows into a staging table and do a TSQL UPDATE.
I think I saw some benchmarking results that concluded MERGE was still faster than this scenario.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 22, 2012 at 6:27 am
Thanks for the clarification! Where I work, one place where a MERGE Statement I think would really work is within a Trigger, have any of you used a MERGE statement inside a trigger? (I think I am going to attempt this today on one of our DEV boxes).
Thanks Again for the replies...
June 22, 2012 at 6:30 am
GBeezy (6/22/2012)
Thanks for the clarification! Where I work, one place where a MERGE Statement I think would really work is within a Trigger, have any of you used a MERGE statement inside a trigger? (I think I am going to attempt this today on one of our DEV boxes).Thanks Again for the replies...
Why would you use a MERGE statement in a trigger?
June 22, 2012 at 6:37 am
We have triggers on certain tables that kick of a real time integration between our systems. For Example, when a Customer's Name is changed in one database and we need to get it to the "Rest of the world", a trigger is what kicks off the whole process and then SSIS takes care of the real time integration.
Currently we have a triggers that look to see if something exists in a Sync table (seems like a when matched of the MERGE statement) if it does it performs a simple update, if it does not exist (when not matched) it inserts the needed data.
It seems to me that a MERGE statement would work for this and from all that I have been reading, a MERGE statement would perform better, is easier to read (although that could be debatable to someone who has never seen a MERGE statement)...
Just something I want to try.
June 22, 2012 at 8:12 am
Yes, MERGE would be applicable to that scenario.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply