Fun with BCP

Here’s a quick and dirty way to export a table to a text file with SQL Server. With some minor tweaking, it should also work for views and stored procedures that return result sets.

Here is a simple method of exporting all the data from SQL Server table to a Text File

CREATE Procedure BCP_Text_File
( 
@table varchar(100), 
@FileName varchar(100) 
) 
as 
If exists(Select * from information_Schema.tables where <table_name=@table>)
    Begin
        Declare @str varchar(1000) 
        set @str=’Exec Master..xp_Cmdshell ”bcp “Select * from ‘+db_name()+’..’+@table+’” queryout “‘+@FileName+’” -c”’ 
        Exec(@str) 
    end
else
    Select ‘The table ‘+@table+’ does not exist in the database’

Execute this procedure by giving Table Name and the File Name

EXEC BCP_Text_File ‘Employee’,’C:\emp.txt’

Now all the data from Employee table will be exported to the text file which will be located at C:\emp.txt

_[from WebLogs @ SqlJunkies.com]
_