Because SQLCMD is a commandprompt utility it can be used to automate actions. Consider a scenario in which you need to execute 50 different scriptfiles to update an application database.
- In SSMS you can open each scriptfile and execute it. Each time you need to open the file, press the [execute] button and close the file. This is very time consuming and boring to do.
- With the help of some dynamic coding you can generate a batchfile with all the needed SQLCMD command lines that execute each scriptfile. To execute the complete update process of 50 files you only have to execute the single batch file.
You'll have to generate the dynamic code to generate the batchfile, but once you have created it, it can be used time-and-time again to generate new batchfiles.
SSMS is a good utility to do ad-hoc work and when you need an overview of settings/results.
SQLCMD is a good utility to automate your actions.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **