Home > MSSQL > tsql – Query MSSQL from the command line

tsql – Query MSSQL from the command line

Run the same query on a MSSQL database several times.


I had to get data for various months from a table on MSSQL. Rather than sit down in front of the computer and manually change each value and re-run the query using SQL Query Tool(?) I opted to use the command line. So I moved to a linux box that could:
1. Access the Windows box that had MSSQL server installed.
2. Had tsql installed.

First I wrote the query.
$cat query

use cars 
SELECT LEFT(RecordTime, 11) AS Date, COUNT(CarType) FROM sales WHERE datepart(yyyy, RecordTime) = '2010' AND datepart(mm, RecordTime) = '$month' GROUP BY LEFT(RecordTime, 11) ORDER BY LEFT(RecordTime, 11)

$month is the variable that is going to change

On the linux box.
Check that I could connect remotely

shell>tsql -S [server-ip-address] -U [username] -P [password]
locale is “C”
locale charset is “ANSI_X3.4-1968”
1> exit

Once I knew I could connect I just needed to run the queries. So on the command line I did

shell>for month in 01 05 10 11 12; do sed “s/\$month/$month/” query | tsql -S [server-ip-address] -U [username] -P [password] > results_$month.csv; done

Thats it.

Categories: MSSQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: