开发者

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

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


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.


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'


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜