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

Question

How to output a table to a txt file using tsql? I do not want to use DTS or SSIS in this case.

Answer 1

Bcp

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

• out can be replaced by a query if a sql query is used or in case of data loading.
• -T Windows authentication, replacing with -u and -p for sql auth
• -c is output as text instead of binary
• -r - string terminator parameter
• -t os tce field terminator option
• -S to specify the default server

thats about all the really useful options for export, I think.

Answer 2

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

Answer 3

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 4

I do this all the time in SQLCMD mode. Here is an example:

 ----------------------------- --Generate State seeds ----------------------------- -- This is the path and file where you want the scripts to land. :setvar OutDir "C:\Dev\Sandbox\GenTest\Scripts\" :setvar OutFile "dbo.State.seed.sql" SET NOCOUNT ON; GO :out $(OutDir)$(OutFile) SELECT 'INSERT [State] ([StateId], [StateCd], [Description]) VALUES (' + CAST( [StateId] AS VARCHAR(2)) + ', ''' + [StateCd] + '''' + ', ''' + [Description] + '''' + ');' FROM [State]; GO --this 'GO' is vital for this to work correctly. :out stdout 

- Graeme