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.

Solution 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.

Solution 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'

Solution 3

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

Solution 4

I do this all the time with 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

View more solutions