EXPORT SQL SERVER DATA TO TEXT FILE

It is real simple to export data to text file from SQL Server. There are quite few ways that this task can be accomplished. 

1	OPENROWSET
2	BCP
3	SSIS

First we will se how to use the “openrowset”. You can either export data from the output of a “select” statement or from variables as static values with in the proc.

insert openrowset (‘Microsoft.Jet.OLEDB.4.0′, ‘Text;Database=C:\Temp’,filename#txt) Select col_1 from table_a

insert openrowset (‘Microsoft.Jet.OLEDB.4.0′, ‘Text;Database=C:\Temp’,filename#txt) values(@col_1)

The caveat is that you need to have the text file available with the same name exactly in that particular folder with the column headers that you are going to export. This is required because SQL Server open the text file as remote data source.

The second way would be to use the bcp utility within the proc. First let’s see how to implement this.

declare @sql varchar(8000) SELECT @sql = ‘bcp “select * from talbe_a” queryout c:\temp\filename.txt -c -t, -T -S <servername>’ exec master..xp_cmdshell @sql

By doing this way you do not need the requirement of file existence in that folder as “BCP” creates the file. But wait; there is a caveat in this way too, that the Sql Server instance needs to be set with few options. This can be implemented using Sql Server surface configuration or by using sp_configure proc.

EXEC sp_configure ‘show advanced options’, 1 GO reconfigure EXEC sp_configure ‘xp_cmdshell’, 1 GO reconfigure

The third way would be to create a DTS package using Sql Server Integration Service.

I hope this would help to some degree in getting the data exported to text file.

Source : http://thiagsundar.wordpress.com/export-data-to-text-file/