SQL Server Performance Forum – Threads Archive

Merge

Declare @Code varchar(3) Declare @Description varchar(15) Set @Code = ‘ABC’ Set @Description =’Merge’
Merge into tblData T USING TblData S ON S.Code = @Code WHEN MATCHED THEN UPDATE SET t.Description = @Description WHEN NOT MATCHED THEN INSERT (Code,Description) VALUES (@Code,@Description); select * from tbldata tblData table does not have records. If I run above command I should get one record. However, I getting no records. Is above statment is incorrect. —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Have you referred in SQL 2008 BOL in this case,http://blog.benhall.me.uk/2007/06/sql-server-2008-sql-merge-statement.html fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Wesley Backelant has an excellent example, works for me on 2008 too http://dis4ea.blogspot.com/2007/06/fun-with-new-merge-statement.html

MERGE … USING … seems to work with existing rows between existing tables, not with a list of variables as the source. Perhaps if you convert the list of variables to a derived table with aliases — MERGE INTO tblData T
USING (SELECT @Code Code, @Description Description) S ON T.Code = S.Code
WHEN MATCHED THEN UPDATE
SET t.Description = s.Description
WHEN NOT MATCHED THEN INSERT (Code,Description)
VALUES (Code, Description); I’m still not convinced that we’re writing less code with this syntax …

For the record: IF EXISTS (SELECT * FROM tblData WHERE Code = @Code)
UPDATE tblData SET Description = @Description WHERE Code = @Code
ELSE
INSERT INTO tblData (Code, Description) VALUES (@Code, @Description)

I just wanted to get hands on expereince on MERGE

quote:Originally posted by Adriaan For the record: IF EXISTS (SELECT * FROM tblData WHERE Code = @Code)
UPDATE tblData SET Description = @Description WHERE Code = @Code
ELSE
INSERT INTO tblData (Code, Description) VALUES (@Code, @Description)

—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Yes, but did you test the script that I posted before that? Like I stated, the second script was "for the record".[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

check out the link i posted, Dinesh, it works a treat.

quote:Originally posted by Adriaan Yes, but did you test the script that I posted before that?

I will check that tonight and let you know allong with other scripts. thanks guys
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by thomas check out the link i posted, Dinesh, it works a treat. <hr height="1" noshade id="quote"></blockquote id="quote">The problem presented by Dinesh is that he wants to merge a value list with rows in one table, rather than merging rows between two tables. Neither of the two links seem to cover that scenario.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

quote:Originally posted by Adriaan MERGE … USING … seems to work with existing rows between existing tables, not with a list of variables as the source. Perhaps if you convert the list of variables to a derived table with aliases — MERGE INTO tblData T
USING (SELECT @Code Code, @Description Description) S ON T.Code = S.Code
WHEN MATCHED THEN UPDATE
SET t.Description = s.Description
WHEN NOT MATCHED THEN INSERT (Code,Description)
VALUES (Code, Description); I’m still not convinced that we’re writing less code with this syntax …

Yes it is working —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by dineshasanka Yes it is working<hr height="1" noshade id="quote"></blockquote id="quote">See how far a little lack of training will get you. I should consider answering questions about Oracle syntax now.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan MERGE … USING … seems to work with existing rows between existing tables, not with a list of variables as the source. Perhaps if you convert the list of variables to a derived table with aliases — MERGE INTO tblData T USING (SELECT @Code Code, @Description Description) S ON T.Code = S.Code WHEN MATCHED THEN UPDATE SET t.Description = s.Description WHEN NOT MATCHED THEN INSERT (Code,Description) VALUES (Code, Description); I’m still not convinced that we’re writing less code with this syntax … <hr height="1" noshade id="quote"></blockquote id="quote"> I don’t think we are writing less code but may be performance gain [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Syntax looks like plain enlish…. MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU I don’t think we are writing less code but may be performance gain [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Syntax looks like plain enlish….<hr height="1" noshade id="quote"></blockquote id="quote">Under the hood, I’m pretty sure the SQL engine expands it to a set of insert, update and delete queries to ensure the basic data integrity. It would be interesting to see a trace of that. And if you call that plain English, you must be a computer geek.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] LOL, thats a good laugh Adriaan. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

The following sentences looks to me plain Enlish… I am not a computer geek [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It has often been discussed if something like "UPSERT" make sense. I do think so. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] The only advantage I see currently when using WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT is, that you use a compiled, and hopefully highly optimized machine code to perform the operation, while the "traditional" approach uses interpreted T-SQL code. – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] funny enough MS developers do keep such funny names until they compile the components in SQL, one of my friend there mentioned the same. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

OK then – someone please run the MERGE syntax in the 2008 version, and report the trace.

]]>