0

I'm using tsql (installed with FreeTDS) and I'm wondering if there's a way to run a SQL script in tsql from the command line and get the result in a text file.

For example, in psql I can do:

psql -U username -C "COPY 'SELECT * FROM some_table' to 'out.csv' with csv header"

Or:

psql -U username -C "\i script.sql"

And in script.sql do:

\o out.csv
SELECT * FROM  some_table;

Is there a way for doing this in tsql? I have read the linux man page and search everywhere but I just don't find a way.

Topo
  • 4,783
  • 9
  • 48
  • 70

4 Answers4

6

I think, you can try "bsqldb", see http://linux.die.net/man/1/bsqldb

Alexander Sigachov
  • 1,541
  • 11
  • 16
  • This is definitely the best answer. Thank you! `brew install freetds` on my Mac brought `bsqldb` down with it already. ex usage: `bsqldb -S $MSSQL_SERVER -U $MSSQL_USERNAME -P $MSSQL_PASSWORD -i test.sql` – mattmc3 Sep 27 '16 at 13:05
5

I really didn't find how to do this and I'm starting to thinks it just can't be done in tsql. However I solved for my specific problem redirecting the stdin and stdout. I use a bash script like this:

tsql connection parameters < tsql_input.sql > tsql_output.csv
lines=`cat tsql_output.csv | wc -l`

# The output includes the header info of tsql and the "(n number of rows)" message
# so I use a combination of head and tail to select the lines I need
headvar=$((lines-2))
tailvar=$((lines-8))

head -$headvar tsql_output.csv | tail -$tailvar tsql_output.csv > new_output_file.csv

And that saves just the query result on 'new_output_file.csv'

Topo
  • 4,783
  • 9
  • 48
  • 70
1
freebcp "select * from mytable where a=b" queryout sample.csv -U anoop -P mypassword -S hostname.com -D dbname -t , -c

This command woks like a charm. Kudos to FreeTDS...

loki
  • 9,816
  • 7
  • 56
  • 82
Anoop Patil
  • 11
  • 1
-1
tsql -U username -P password -p 1433 > out.csv <<EOS SELECT * FROM some_table; go EOS
Pang
  • 9,564
  • 146
  • 81
  • 122
Nagendra Kumar
  • 25
  • 1