What are the challenges you face when working across database platforms? Take the survey
Options

Align = in MERGE statement

Hi all

We've been using SQL Prompt styles to give our code a standardised format but there's one small niggle.

We can align the "=" in the FROM clause on joins but, when using an MERGE statement, we can't find the option to align the "=" in the "WHEN MATCHED" section.

Does anyone have any pointers on this?

Thanks

Richard
Tagged:

Answers

  • Options
    Hi RichardMGreen,

    Thanks for posting on the Redgate forums.

    It might be that this is a feature request, but to confirm could you please show a before and after code sample of what you are expecting to see?
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • Options
    rmg1rmg1 Posts: 11 Bronze 1
    Hi Dan

    This is what we currently get:-

    WHEN MATCHED THEN

    UPDATE

    SET

    TARGET.fkPatientLocalID= SOURCE.PatientLocalID

    ,TARGET.fkStaffLocalID= SOURCE.StaffLocalID

    ,TARGET.EpisodeStartDateTime= SOURCE.EpisodeStartDateTime

    ,TARGET.EpisodeEndDateTime= SOURCE.EpisodeEndDateTime

    ,TARGET.MainSpecialtyLocalCode= SOURCE.MainSpecialtyLocalCode

    ,TARGET.MainSpecialtyLocalDescription= SOURCE.MainSpecialtyLocalDescription

    ,TARGET.MainSpecialtyNationalCode= SOURCE.MainSpecialtyNationalCode

    ,TARGET.MainSpecialtyNationalDescription= SOURCE.MainSpecialtyNationalDescription

    ,TARGET.TreatmentFunctionLocalCode= SOURCE.TreatmentFunctionLocalCode

    ,TARGET.TreatmentFunctionLocalDescription= SOURCE.TreatmentFunctionLocalDescription

    ,TARGET.TreatmentFunctionNationalCode= SOURCE.TreatmentFunctionNationalCode

    ,TARGET.TreatmentFunctionNationalDescription= SOURCE.TreatmentFunctionNationalDescription

    ,TARGET.IsDeleted= SOURCE.IsDeleted

    ,TARGET.SYSDateLastUpdated= @Today



    This is what we'd like to see:-


     

    WHEN MATCHED THEN

    UPDATE

    SET

    TARGET.fkPatientLocalID                         = SOURCE.PatientLocalID

    ,TARGET.fkStaffLocalID                          = SOURCE.StaffLocalID

    ,TARGET.EpisodeStartDateTime                    = SOURCE.EpisodeStartDateTime

    ,TARGET.EpisodeEndDateTime                      = SOURCE.EpisodeEndDateTime

    ,TARGET.MainSpecialtyLocalCode                  = SOURCE.MainSpecialtyLocalCode

    ,TARGET.MainSpecialtyLocalDescription           = SOURCE.MainSpecialtyLocalDescription

    ,TARGET.MainSpecialtyNationalCode               = SOURCE.MainSpecialtyNationalCode

    ,TARGET.MainSpecialtyNationalDescription        = SOURCE.MainSpecialtyNationalDescription

    ,TARGET.TreatmentFunctionLocalCode              = SOURCE.TreatmentFunctionLocalCode

    ,TARGET.TreatmentFunctionLocalDescription       = SOURCE.TreatmentFunctionLocalDescription

    ,TARGET.TreatmentFunctionNationalCode           = SOURCE.TreatmentFunctionNationalCode

    ,TARGET.TreatmentFunctionNationalDescription    = SOURCE.TreatmentFunctionNationalDescription

    ,TARGET.IsDeleted                                = SOURCE.IsDeleted

    ,TARGET.SYSDateLastUpdated                      = @Today


    Hopefully the above makes sense.

    If not, I can post screenshots if that is an option.


    Thanks

    Richard

  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    Hi Dan


    Found the option to attach a screenshot.

    This is what we'd like to see.


    Thanks

    Richard

  • Options
    I have spoken to the team about this and currently, there isn't a way for this to be achieved inside SQL Prompt. We have created a feature request for this but I can't guarantee that this will gather enough momentum to make it into the product in the future.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    Thanks Dan.


    Is there any way for me to help this gain some momentum?


    Regards

    Richard

  • Options
    EricFrankenEricFranken Posts: 1 New member
    Dan B said:
    I have spoken to the team about this and currently, there isn't a way for this to be achieved inside SQL Prompt. We have created a feature request for this but I can't guarantee that this will gather enough momentum to make it into the product in the future.



    We think this is one of the biggest flaws of the formatting prompt style. This is also the case for an update statement. What is the status the feature request?
Sign In or Register to comment.