SQL Server : export query as a .txt file

Question

I am trying to export my SQL Server query results into a folder in .txt format (this is for an automated job)

I know the equivalent in MySQL works with INTO OUTFILE. Does anyone know the best way to do this in SQL Server 2008 Management Studio?

SELECT DISTINCT RTRIM (s1.SGMNTID) AS 'AccCode',RTRIM (s1.DSCRIPTN) AS 'CodeDesc', CASE
    WHEN  s1.SGMTNUMB = '1' THEN '1' 
    WHEN s1.SGMTNUMB = '2' THEN '2'
    WHEN s1.SGMTNUMB = '3' THEN '110'
    WHEN s1.SGMTNUMB = '4' THEN '4'
    WHEN s1.SGMTNUMB = '5' THEN '120'
    END AS 'AccountType_id',
CASE WHEN s1.SGMTNUMB = '2' 
THEN LEFT(s1.SGMNTID, 2)
ELSE 'DEFAULT'
END AS 'AccGroupName'

 FROM GL40200 s1

UNION 

SELECT  REPLACE ([ACTNUMBR_1]+'-'+ [ACTNUMBR_2]+'-'+ [ACTNUMBR_3]+'-'+[ACTNUMBR_4]+'-'+    [ACTNUMBR_5],' ', '') AS 'AccCode',
 '' AS 'CodeDesc',
 '0' AS 'AccountType_id',
 'Default' AS 'AccGroupName'
FROM GL00100 a

INTO OUTFILE 'C:\Users\srahmani\verian/myfilename.txt'

Answer 1

You do this in the SSMS app, not the SQL.

In the toolbar select:

Query --> Results To --> Results To File

Then Execute the SQL statements and it will prompt you to save to a text file with an .rpt extension. Open the results in a Text Editor.

Answer 2

You can use windows Powershell to execute a query and output it to a text file

Invoke-Sqlcmd -Query "Select * from database" -ServerInstance "Servername\SQL2008" -Database "DbName" > c:\Users\outputFileName.txt