Formatting SQL statements that contain WITH CHANGE_TRACKING_CONTEXT

buinauskas

So I've got SQL statement as follows:

WITH CHANGE_TRACKING_CONTEXT(@ContextID)
UPDATE lcoc
SET lcoc.cmpName = tmp.cmpName
  , lcoc.ctyCode = tmp.ctyCode
  , lcoc.cmpYearDead = tmp.cmpYearDead
  , lcoc.cmpSuccessorESCI = tmp.cmpSuccessorESCI
  , lcoc.cmpCompanyStatus = tmp.cmpCompanyStatus
  , lcoc.cmpYearEstablished = tmp.cmpYearEstablished
  , lcoc.cmpMonthFinYearEnd = tmp.cmpMonthFinYearEnd
  , lcoc.cmpMonthAnnualReport = tmp.cmpMonthAnnualReport
  , lcoc.cmpFinCurrency = tmp.cmpFinCurrency
  , lcoc.cmpFinUnit = tmp.cmpFinUnit
  , lcoc.ysnNoBrand = tmp.ysnNoBrand
  , lcoc.ysnAlone = tmp.ysnAlone
  , lcoc.ysnNoNBO = tmp.ysnNoNBO
  , lcoc.ysnNoGbo = tmp.ysnNoGbo
  , lcoc.ysnNoResearch = tmp.ysnNoResearch
  , lcoc.ysnNoPublish = tmp.ysnNoPublish
  , lcoc.ysnNoShare = tmp.ysnNoShare
  , lcoc.ysnNoSize = tmp.ysnNoSize
  , lcoc.cmpDateReadyForReview = tmp.cmpDateReadyForReview
  , lcoc.ysnReadyToLoad = tmp.ysnReadyToLoad
  , lcoc.cmpAddress_1 = tmp.cmpAddress_1
  , lcoc.cmpAddress_2 = tmp.cmpAddress_2
  , lcoc.cmpAddress_3 = tmp.cmpAddress_3
  , lcoc.cmpPObox = tmp.cmpPObox
  , lcoc.cmpCityTown = tmp.cmpCityTown
  , lcoc.cmpStateCounty = tmp.cmpStateCounty
  , lcoc.cmpPostalCode = tmp.cmpPostalCode
  , lcoc.cmpTelephone = tmp.cmpTelephone
  , lcoc.cmpTelefax = tmp.cmpTelefax
  , lcoc.cmpEmail = tmp.cmpEmail
  , lcoc.cmpKey = tmp.cmpKey
  , lcoc.usrID = tmp.usrID
  , lcoc.flcID = tmp.flcID
  , lcoc.cmpKeyIssueDate = tmp.cmpKeyIssueDate
  , lcoc.cmpKeyExpireDate = tmp.cmpKeyExpireDate
  , lcoc.cmpTelex = tmp.cmpTelex
  , lcoc.cmsStatusID = tmp.cmsStatusID
FROM dbo.EMMA_tlkpCompanyCore AS lcoc
INNER JOIN #tlkpCompanyCore AS tmp
    ON tmp.ESCI = lcoc.ESCI;

But when I format my code using SQL Prompt, I get this:

WITH CHANGE_TRACKING_CONTEXT(@ContextID)UPDATE lcoc
                                        SET lcoc.cmpName = tmp.cmpName
                                          , lcoc.ctyCode = tmp.ctyCode
                                          , lcoc.cmpYearDead = tmp.cmpYearDead
                                          , lcoc.cmpSuccessorESCI = tmp.cmpSuccessorESCI
                                          , lcoc.cmpCompanyStatus = tmp.cmpCompanyStatus
                                          , lcoc.cmpYearEstablished = tmp.cmpYearEstablished
                                          , lcoc.cmpMonthFinYearEnd = tmp.cmpMonthFinYearEnd
                                          , lcoc.cmpMonthAnnualReport = tmp.cmpMonthAnnualReport
                                          , lcoc.cmpFinCurrency = tmp.cmpFinCurrency
                                          , lcoc.cmpFinUnit = tmp.cmpFinUnit
                                          , lcoc.ysnNoBrand = tmp.ysnNoBrand
                                          , lcoc.ysnAlone = tmp.ysnAlone
                                          , lcoc.ysnNoNBO = tmp.ysnNoNBO
                                          , lcoc.ysnNoGbo = tmp.ysnNoGbo
                                          , lcoc.ysnNoResearch = tmp.ysnNoResearch
                                          , lcoc.ysnNoPublish = tmp.ysnNoPublish
                                          , lcoc.ysnNoShare = tmp.ysnNoShare
                                          , lcoc.ysnNoSize = tmp.ysnNoSize
                                          , lcoc.cmpDateReadyForReview = tmp.cmpDateReadyForReview
                                          , lcoc.ysnReadyToLoad = tmp.ysnReadyToLoad
                                          , lcoc.cmpAddress_1 = tmp.cmpAddress_1
                                          , lcoc.cmpAddress_2 = tmp.cmpAddress_2
                                          , lcoc.cmpAddress_3 = tmp.cmpAddress_3
                                          , lcoc.cmpPObox = tmp.cmpPObox
                                          , lcoc.cmpCityTown = tmp.cmpCityTown
                                          , lcoc.cmpStateCounty = tmp.cmpStateCounty
                                          , lcoc.cmpPostalCode = tmp.cmpPostalCode
                                          , lcoc.cmpTelephone = tmp.cmpTelephone
                                          , lcoc.cmpTelefax = tmp.cmpTelefax
                                          , lcoc.cmpEmail = tmp.cmpEmail
                                          , lcoc.cmpKey = tmp.cmpKey
                                          , lcoc.usrID = tmp.usrID
                                          , lcoc.flcID = tmp.flcID
                                          , lcoc.cmpKeyIssueDate = tmp.cmpKeyIssueDate
                                          , lcoc.cmpKeyExpireDate = tmp.cmpKeyExpireDate
                                          , lcoc.cmpTelex = tmp.cmpTelex
                                          , lcoc.cmsStatusID = tmp.cmsStatusID
                                        FROM dbo.EMMA_tlkpCompanyCore AS lcoc
                                        INNER JOIN #tlkpCompanyCore AS tmp
                                            ON tmp.ESCI = lcoc.ESCI;

I couldn't find any option to put UPDATE to a new line. I'm using latest SQL Prompt build. This isn't too bad, but makes code go wide and I just hate horizontal scrolling.

Is there an option that I simply cannot find or this should be a feature request?

Tianjiao_Li

Hi @buinauskas

Thanks for posting.

Our development team has confirmed it's a feature request. Could you please post this idea on our Uservoice forum? As soon as you post it other users can vote for it and it is easier for our product team to prioritize it as per the demand?

Sorry for the inconvenience caused.

buinauskas

@Tianjiao_Li Hello, that's not a huge inconvenience at all. I'll submit it!

Thanks,
Evaldas

Tianjiao_Li

Hi @buinauskas

Many thanks!