Red Gate forums :: View topic - Format MERGE statements
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

Format MERGE statements

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jela



Joined: 04 Dec 2013
Posts: 7

PostPosted: Wed Dec 04, 2013 1:26 pm    Post subject: Format MERGE statements Reply with quote

Hi,

while searching the forum regarding format options for MERGE statements I only came across an entry for SQL Prompt 5:

http://www.red-gate.com/MessageBoard/viewtopic.php?t=17275&highlight=prompt+merge

Are there any news regarding this topic? Has anybody found a way to properly format MERGEs?

Kind regards
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Thu Dec 05, 2013 11:05 am    Post subject: Reply with quote

Hi jela,
I think 2 of his points are fixed in the latest release of SQL Prompt. It looks to me like to INSERT indentation is still off though so I'll look into that for you now.

Do you have a specific statement that's causing problems?

Thanks,
Aaron.
Back to top
View user's profile Send private message
jela



Joined: 04 Dec 2013
Posts: 7

PostPosted: Fri Dec 06, 2013 2:41 pm    Post subject: Reply with quote

Hi Aaron,
thanks for the reply and looking into things.

Code:

MERGE INTO TableB AS dest
  USING TableA AS src
  ON dest.Col1 = src.Col1
    AND dest.Col2 = src.Col2
  WHEN MATCHED
    THEN
UPDATE
     SET
        Col3 = src.Col3
      , Col4 = src.Col4
  WHEN NOT MATCHED BY TARGET
    THEN
INSERT  (
          Col1
        , Col2
        , Col3
        , Col3
        )
     VALUES
        (
          SomeValue1
        , SomeVlaue2
        , SomeValue3
        , SomeValue4
        );


The above is an example statement, which unfortunately isn't 100% formatted as it is in SSMS. Anyway, I'll try to explain, what looks a bit odd.

USING, ON, WHEN MATCHED, WHEN NOT MATCHED are indented, whereas I would understand them to be part of the MERGE statement itself. The keywords THEN UPDATE SET and THEN INSERT are each on a new line - I understand, that it comes down to personal preferences here, but I would see UPDATE SET and INSERT as actions, whereas THEN would belong to the WHEN clause itself. The openening bracket after VALUES is on a new line, whereas the opening bracket after INSERT is on the same line as the keyword.

Maybe it becomes more clear, if I try to show, what I have in mind:

Code:

MERGE INTO ...
USING          ...
ON               ... = ...
                   AND ... = ...
WHEN MATCHED THEN
    UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
                  ...
                , ...
                )
    VALUES (
                   ...
                 , ...
                 )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
;


If there already is a way to achieve a formatting similar to this, I would appreciate some hints on how to accomplish this.

Thanks again for your help.

Kind regards,
Jens.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Fri Dec 06, 2013 5:45 pm    Post subject: Reply with quote

I've changed the merge formatting to include your suggestions if you'd like to give this private build a try.

Thanks,
Aaron.
Back to top
View user's profile Send private message
jela



Joined: 04 Dec 2013
Posts: 7

PostPosted: Wed Dec 18, 2013 10:00 am    Post subject: Reply with quote

Hi Aaron,

thanks for your changes.

We have been using the private build for a few days now and think, the formatting looks better this way. Thanks again.

Will this be implemented into an upcoming version or do you plan on providing some more options for SQL Prompt?

Kind regards,
Jens
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Wed Dec 18, 2013 3:15 pm    Post subject: Reply with quote

Hi Jens,
We've just released 6.2.0.241 which includes these changes for how MERGE is formatted. See this post for more details.

Thanks,
Aaron.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group