SQLCMD is a nice utility to automate the SQL scripts execution.I became the fan of it years ago when I first used it for automating database creation and version upgrade of databases. The past sql automations using sqlcmd.exe were relatively simple. Most of those were using tokens and those tokens will be replaced with actual values when the execution starts. The values for tokens will be supplied by initiating programs and those programs may be of .bat, .vbs or .ps1 files at the most it will be invoked from the installers.
Recently we came to a situation where a branching is required in SQLCMD scripting. Basically we need to change the value of the token at runtime based no a database SELECT query. At that moment I realized that sqlcmd doesn't have branching statements. Team's initial decision to mitigate it was to call the database from the initiator programs.But those will make the .bat files really heavy or may need to write another PowerShell script or exe to do the job and get the value for sqlcmd token.
But a tight google exercise unveiled the below link which explains how to imitate the branching in sqlcmd. Its nothing but creating dynamic .sql file based on query result and executing the same from its creator sql file. I don't think I need to replicate the details again. So just open the link and have fun.
No comments:
Post a Comment