SQL Server : export from file.sql to a flat file

Question

I am looking for assistance in performing an export of data via an SQL Server query into a flat file which is delimited by a | (pipe).
I have done this in Sybase SQL without problems using the OUTPUT function within my code:

SELECT ItemName
FROM Products
WHERE ItemName = 'Gizmo'
; OUTPUT TO Z:\Export.txt FORMAT ASCII DELIMITED BY '\xA6'

Does SQL Server have such a function and if so, how it is used?

Update

I should have clarified this a bit more. I would like to execute this file.sql statement via a VB Script in order to make a manual process into an automated one. Any manual functions within SQL Server Management Studio will not work. xp_cmdshell is not an option either as the DBA will not allow it.

score 5 · Answer 1 · answered Aug 02 '13 at 18:22

In SSMS go to Tools -> Options -> Query Results -> SQL Server -> Results to Text
On this pane choose Output format: Customer delimiter, and enter '|'
Return to your query and select Query -> Results to File (Ctrl+Shift+F)
Execute your query, you will be asked to select the file path and name

File will be saved with a .rpt extension, you can alter it to .txt

score 1 · Answer 2 · answered Aug 02 '13 at 20:44

Based on your update:

I should have clarified this a bit more. I would like to execute this file.sql statement via a VB Script in order to make a manual process into an automated one. Any manual functions within SQL Server Management Studio will not work. xp_cmdshell is not an option either as the DBA will not allow it.

Will your DBA allow you to use SSIS (or Data Tools) and do have that capability? If so, you can create a DATA FLOW task in SSIS (or Data Tools) that will export the query to a flat file, have a job call the package, and your VB script execute sp_start_job to execute the job that calls the package.

score 1 · Answer 3 · edited May 23 '17 at 12:33

I have to export querys into flatfiles all the time so this is my solution. Basically I create a procedure that has the Query (remember to put in tables full name), File Name, Dir Path as parameters. Values are separated by "|" by default but you can change it..

Don't know if these are best practices or not, It works for me and security is not a issue with the db's I'm working with.

BCP utillity does the trick almost by itself.. Before creating the procedure be sure to set the right permissions: this and this.

CREATE PROCEDURE [dbo].[export_table] 
@query VARCHAR(8000)
,@out_file VARCHAR(8000)
,@out_dir VARCHAR(8000)
AS
BEGIN

  DECLARE @temp NVARCHAR(4000)
  DECLARE @query_body VARCHAR(8000)
  DECLARE @header VARCHAR(8000)
  DECLARE @full_header VARCHAR(8000)
  DECLARE @copy VARCHAR(8000)
  DECLARE @del VARCHAR(8000)
  DECLARE @del1 VARCHAR(8000)

  -- CREATE AND EXPORT BODY

  -- drop temp table (when running procedure in same instance)

  IF OBJECT_ID('tempdb..##temp') IS NOT NULL 
  DROP TABLE ##temp 

  -- populate temp with just 1 line so we can take out the header

  SET @temp = 'SELECT TOP 1 * INTO ##temp FROM (' + COALESCE(@query,'') + ') a'  

  -- set up the bcp query removing idents so you dont have to write the query in one line

  SET @query_body = 'bcp "' + COALESCE(@query,'') + '" queryout ' + @out_dir + 'qBody.txt -CACP -T -c -t"|" '
  SET @query_body = REPLACE(REPLACE(@query_body, CHAR(13), ' '), CHAR(10), ' ')
  EXECUTE sp_executesql @temp
  EXEC master..xp_cmdshell @query_body

  -- CREATE AND EXPORT HEADER

  -- set the header using system tables and 
  -- temp table previously created (alias = name) and use bcp to expor the header 

  SELECT @header = 
    COALESCE(@header + ''', ', '') + '''' + name  
    FROM (
      SELECT name 
      FROM tempdb.sys.columns 
      WHERE 
        object_id = object_id('tempdb..##temp') 
    ) b
  SET @header = @header + ''''
  SET @full_header = 'bcp "select '+ @header +'" queryout ' + @out_dir + 'qHeader.txt -CACP -T -c -t"|" '

  -- COPY NEW FILE FROM HEADER AND BODY AND DELETE USED FILES (USING WINDOWS CMD) 

  SET @copy = 'copy /b "' + @out_dir + 'qHeader.txt" + "' + @out_dir + 'qBody.txt" "' + @out_dir + @out_file + '.txt"'
  SET @del = 'del "' + @out_dir + 'qHeader.txt"'
  SET @del1 = 'del "' + @out_dir + 'qBody.txt"'
  EXEC master..xp_cmdshell @full_header
  EXEC master..xp_cmdshell @copy
  EXEC master..xp_cmdshell @del
  EXEC master..xp_cmdshell @del1
END

GO

-- EXEMAPLE OF USAGE

exec export_table
'select 
    column1
    ,column2 as ''escape_quotes'' 
from cnes.dbo.stg_vinculo'
,'file_name'
,'C:\'

score 0 · Answer 4 · answered Aug 02 '13 at 18:32

Here is the command:

Exec master..xp_cmdshell 'bcp "SELECT * FROM test12" queryout "c:\tempexportfile.txt" -c -T -f "c:\Fotmat.txt"'

where -f is there for a format file. This is the link on how to create a format file.http://msdn.microsoft.com/en-us/library/ms178129.aspx

SQL Server : export from file.sql to a flat file

4 Answers4