How do I output a table to a txt file using tsql? I don't want to use DTS or SSIS in this instance.
Add a comment
|
4 Answers
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.
Comments
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'
Comments
You can also to press CTRL+SHIFT+F to redirect your SQL Server Management Studio output to a file.
Comments
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
