How do I output the results of a simple SQL statement to a text file??
I have this simple SELECT statement but am struggling to output it to a file using SQL Server 2005:
--QUOTER QUOTE DETAILS
SELECT dbo.quoteproduct.quoteid, dbo.quoteproduct.productgroupid,
dbo.product.productname, dbo.product.description, dbo.quoteproduct.price
FROM dbo.quoteproduct, dbo.product
WHERE dbo.quoteproduct.productid = dbo.product.productid;
In other versions of SQL like Sybase I can use the command OUTPUT TO as below but this doesnt work:
OUTPUT TO 'D:\QuoterQuoteDetails.txt' FORMAT ascii DELIMITED BY '|' QUOTE '';
After bing'ing it I have found that you can call an xp_cmdshell to output query results but this only seems to store data in a temporary table not a file.
Id appreciate any help on this. Many thanks Andy
You want BCP, something like:
DECLARE @cmd VARCHAR(8000)
SET @cmd = 'bcp "...Select <fieldlist> from <table>..." queryout'
+' "D:\QuoterQuoteDetails.txt" -c -T'
+' -SLOCALHOST"'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
Should do the trick.
You can also right-click the query window: "Query Options | results | text" formatting options, then (right-click) query window and change "results to" to file.
This is a quick, one-time way redirect the output to a file.
