BCP - Export data to Text File



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


 



Published Monday, August 27, 2007 4:23 PM by Madhivanan

Comments

# re: BCP - Export data to Text File

Monday, December 17, 2007 3:39 PM by lara

what if we want to bcp to the delimited file.

Thanks