Wednesday, October 26, 2005

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) 

If exists(Select * from information_Schema.tables where table_name=@table)
        Declare @str varchar(1000) 
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c''' 
    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 @]

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.