CODEWITHSUNDEEP
sqlsql-servercmdautomation
Roger Steinberg
Roger Steinberg

Reputation: 1604

How to dynamically name output of a sql file in sqlcmd

Objective:

I would like to create a CSV dump on a weekly basis. I would like to name my file dynamically with the date.

The current code I have is:

sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name_$DYNAMIC$DATE.csv

Upvotes: 0

Views: 1714

Answers (2)

lit
lit

Reputation: 16266

PowerShell will let you control the formatting of the date. This removes any hardcoded dependence on a specific locale. If you are on a supported Windows system, PowerShell will be available.

I also replaced name with the standard USERNAME variable value.

FOR /F %A IN ('powershell -NoLogo -NoProfile -Command "Get-Date -Format 'yyyyMMdd-HHmm'"') DO (SET "DYNAMICDATE=%A")
sqlcmd -S INSTANCENAME -i "C:\Users\%USERNAME%\Desktop\test.sql" -o "C:\Users\%USERNAME%\Desktop\%USERNAME%_%DYNAMICDATE%.csv"

Upvotes: 0

rs1
rs1

Reputation: 82

Give this a try:

set timehour=%time:~0,2%
sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

You can try seeing the output by using Echo

set timehour=%time:~0,2%
echo name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

%date% returns current date in short format. The ":~6,4" part is like a SUBSTRING function which returns 4 characters starting from position 6, which returns Year. Similarly, retrieving month, day, hour, minutes using same function and appending all of this together to generate the file name in format "name-YYYYMMDD-HHMM"

Upvotes: 1

Related Questions