Just learnt an awesome feature with in SQLCMD. String Replacement using SQLCMD !! Using -v we can replace strings in the sql file that is about to be executed.

This may not sound great, when you are in a unique situation where you need to replace a particular section of your SQL code with a string literal on the fly right before execution, this works great !! (Using global temp tables, or session variables may work for some situations, but not here)

Let’s say you have a SQL script with a bunch of SQL code. But there is one section, where the variable value is not obvious until a moment before run time. In our case, path to a file. Path depends on some factors that keep changing for every file. So we need something that allows us to run the same batch file for any file by just providing the path as a parameter to the SQLCMD call.

Example:

Imagine a SQL file that accomplishes some business process.

Sample sql file, let’s say SQLCMD_StringReplace.sql looks like this:

SQLCMD Script File on Disk

The script has the following code:

[sourcecode language=”sql”]
DECLARE @test VARCHAR(50) = ‘test1’
, @test2 VARCHAR(50) = ‘test2’
, @test3 VARCHAR(50) = ‘test3’
, @test4 VARCHAR(50) = ‘test4’

SET @test3 = ‘$(Replace1)’ + ‘ – This is replaced.’
SET @test4 = ‘$(Replace2)’ + ‘ – This is replaced.’

PRINT @test
PRINT @test2
PRINT @test3
PRINT @test4
GO
[/sourcecode]

When you run this command at CMD prompt (or PowerShell), the string literals Replace1 and Replace2 will be replaced with respective stings from -v flag (New String1 & New String2)

[sourcecode language=”c”]
sqlcmd -S 153.60.58.148\PLAY -E -v Replace1 = "New string1" Replace2 = "New string2" -i "C:\Test\SQLCMD_StringReplace.sql"
[/sourcecode]

SQLCMD StringReplace Execution

This is an awesome feature !!

Hope this helps,
_Sqltimes

Hat Tip: My colleague Charlie for pointing this to me