CODEWITHSUNDEEP
sql-servercsvsqlcmdinvoke-sqlcmd
Kajan
Kajan

Reputation: 45

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?

Upvotes: 1

Views: 4452

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

It depends on where your SQL Server is acutally running. It might be quite tricky to write anything to the location you want.

You could read about BCP.

My suggestion is:

Create an UDF (best is inline-UDF!) from all of your queries within your database. Than call them from EXCEL or any other fitting product. You might want to set up an Excel where all your queries are filled one on each Sheet automatically

Upvotes: 1

BeanFrog
BeanFrog

Reputation: 2315

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.

Upvotes: 2

Alec.
Alec.

Reputation: 5525

Convert your current scrips to a Stored Procedure. You can then pass your variables to that and run the query.

If you have SQL Server agent available (SQL standard or better) you can use this to automate the running of the stored procedures.

Otherwise the same can be achieved with Task Scheduler in windows.

As for exporting to CSV this will be useful.

Upvotes: 1

strickt01
strickt01

Reputation: 4048

You could write a batch file that uses sqlcmd:

MSDN sqlcmd

That will allow you to call script files in a loop and output the results to a file.

Upvotes: 1

Related Questions