SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to Test this Simple MERGE Statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JacobPressures
Posting Yak Master

112 Posts

Posted - 03/28/2013 :  16:39:24  Show Profile  Reply with Quote


MERGE Batches b
USING (SELECT ID, FileName, CdbConnection, RdbConnection, CustomerID
FROM Batches
WHERE FileName = 'ex_20121009_unity-ny-a' AND CdbConnection = '\\mwfs1avm\MWPRIVATE\macdog\ex_20121009_unity-ny-a') o
ON o.ID = b.ID
WHEN MATCHED THEN SELECT 'Match Found'
WHEN NOT MATCHED THEN SELECT 'NO Match';

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/28/2013 :  16:54:40  Show Profile  Reply with Quote
Run it?
Go to Top of Page

JacobPressures
Posting Yak Master

112 Posts

Posted - 03/28/2013 :  16:59:35  Show Profile  Reply with Quote
I get this error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SELECT'.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/28/2013 :  17:18:51  Show Profile  Reply with Quote
Here are some links on how to post your question so that we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

JacobPressures
Posting Yak Master

112 Posts

Posted - 03/28/2013 :  17:23:37  Show Profile  Reply with Quote
Actually the problem seems to be that you can't put a SELECT statement there thanks!

I'm trying to figure out how to TEST whether this is working or not.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3639 Posts

Posted - 03/28/2013 :  17:47:10  Show Profile  Reply with Quote
As you found out, MERGE is a DML statement - you can think of it as a combination of update, insert and delete. So the "WHEN" sections have to have one of those.

One way you can test is to write your actual query that has the insert/update/delete's in the WHEN sections, begin a transaction, run the merge statement, examine the results and then rollback.

If it is a hot database/table that you don't want to leave locked (when the transaction is left open), you can use output clause along with your insert/update/deletes to output the results and rollback immediately.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000