How can I read/write to a flat file from inside a SQL Server TSQL script/stored-procedure/trigger?

A. SQL Server doesn't have a handy SPOOL command like Oracle does for writing to files, but there are a number of ways of doing what you want.

1. For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file.
xp_cmdshell "@ECHO test message >> C:\file.fil"

2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell.
xp_cmdshell "bcp <dbname>..<tablename> out c:\file.fil -Usa -P<password> -c"

3. BCP or BULK INSERT (SQL 7 only) can also be used to read in a flat file into a table, from where it can be processed.

4. Write your own extended stored procedure. As this is a C program it can use standard file access commands to achieve whatever you want.

5. Run the select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q"select * from <tablename>" -E -oc:\file.fil'
exec master..xp_cmdshell @str

6. There is a free XP - xp_query_to_file - at www.dbmaint.comĀ 

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.