Search code examples
sql-servercsvsqlcmdinvoke-sqlcmd

SQL - Automatic results to CSV or Text File

I was wondering if anyone can help.

I have a number of queries in SQL (all in separate *.sql files). I wanted to know if there is a way to run these queries automatically or mass run them to be saved to either a csv or txt file?

Also, I have come variables within these queries which will need to be amended on a weekly bases before the queries are run.

Thanks.

KJ

Could you please provide some additional help in relation to the variables? Previously I would declare and set variables as:

DECLARE @TW_FROM DATETIME

DECLARE @TW_TO DATETIME

SET @TW_FROM = '2015-11-16 00:00:00'; SET @TW_TO = '2015-11-22 23:00:00';

How do I do this using sqlcmd?

Solution

  • Yes, you can use sqlcmd to do this.

    First of all - variables. You can refer to your variables in the .sql files using $(variablename) wherever you want to substitue the variable. For example,

    use $(dbname);
select $(columnname) from table1 where column= '$(var1)'

    You then call sqlcmd with the following command (note the argument -v variables)

    sqlcmd -S servername -d database -i "yoursqlfile.sql" -v dbname="database" columnname="column" var1="Fred"

    In order to output this to a file, you tag > filename.txt on the end

    sqlcmd -S servername -d database -i "yoursqlfile.sql" -v dbname="database" columnname="column" var1="Fred" > filename.txt

    If you want to output to a csv, you can also specify the delimiter using the argument -s (note the idfference with the capital S for server). So now we have

    sqlcmd -S servername -d database -s "," -i "yoursqlfile.sql" -v dbname="database" columnname="column" var1="Fred" > filename.csv

    If you want to output several commands to the same csv or txt file, use >> instead of > as it add to teh bottom of the file, rather than replacing it.

    sqlcmd -S servername -d database -s "," -i "yoursqlfile.sql" -v dbname="database" columnname="column" var1="Fred" >> filename.csv

    To run this for several scripts, you can put the statements in a batch file, and then change the variables every week.