How do I output a table to a txt file using tsql?

Question

How do I output a table to a txt file using tsql? I don't want to use DTS or SSIS in this instance.

Answer 1

BCP

bcp MyDb.MySchema.Mytable out myTable.dat -T -c

• out can be replace with queryout if using an sql query, or in if loading data in.
• -T windows authentication, replace with -u and -p for sql auth
• -c outputs as text instead of binary
• -r is the row terminator option
• -t os tje field terminator option
• -S to specify a non default server

thats about all the really usefull options for exporting i think.

Answer 2

Here is the most common answer from a Google search:

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'

Answer 3

You can also to press CTRL+SHIFT+F to redirect your SQL Server Management Studio output to a file.