I dedicate this article to my mother Prajavathi.

This article shows how to import and export from a File to a table/view and vice versa using SQL Server 2000 Bulk Copy Program (BCP) utility.  BCP is the fastest and easiest way to import and export from a file.  This article will be very useful to a developer by simply plugging in this stored procedure wherever it is needed or to execute it to get the result.  I have used BCP for import/ export from a text file and a DOS shell command to execute the BCP command.

Using the parameters which are passed to the stored procedure, a bcp command is built dynamically and passed to the extended stored procedure, xp_cmdshell, which calls the dos shell to execute the bcp command.  The direction can be In or Out.  When you are copying data from a table/view to a file use Out as the direction and when you are copying data from file to a table/ view use In.

Parameter to be passed to the stored procedure

File path: Valid file path where your Sqlserver run not your client machine.

Direction: Pass In for import or Out for export.

Table name/view name:  Valid table/view name which is stored in the current database.

Using the stored procedure you can export or import any table/view from your database to a text file.  BCP is the simplest way to export a single table or view to a text file.  When you export the data to a file you need to give a valid file path and the table/view must be stored in it.  A file will be created and the data will export to the file.  You need to specify the direction as OUT. The file is exported in a format as character, data, and type with tab space as a field separator and the new line as the row terminator.

When you import from the file to a table you need to give a valid file name which contains the data to import.  The table/view should be stored in the current database.  Only a simple view, data from one single table, can be used.  You need to specify the direction as IN.  The format should be of character, data, and type with tab space as the field separator and the new line as the row terminator.

BCP is used to copy the data between the SQL Server database and the data file.  Using BCP you can copy data from a table to a file or from a file to a table.

Please refer to the on-line book under topic bcp utility to know syntax.

Listing 1

BCP pubs.. Employee out c:\temp\export.txt’  -c -SKrishna -Usa -Psa12

In the above example pubs is the database and employee is the table name with the

file path c:\temp\export.txt.

 - c   character data type

–S    server name

-U    username

-P    password

You need to run the bcp script in the command prompt since bcp is an operating system level command.  On execution a new file will be created under the c:\ temp as export.txt and the data will be copied. 

In a similar way you can import the data from the file to the table.  Change the direction as In instead of Out and the file to import should be of valid format since I have used –c for character data type, the default format as /t tab space for field separator and /n new line for row terminator.

Listing 2

BCP pubs..Employee in   c:\temp\import.txt  -c -S Krishna -Usa -Psa12

Using the extended stored procedure xp_cmdshell to run the BCP command, Xp_cmdshell calls the command prompt to run the bcp script.  The xp_cmdshell can be executed only through the sysadmin role or you will need to give permission to the user to execute it.

For example:

Listing 3

Declare @str varchar(4000)
Set @str ='BCP pubs..Employee out c:\temp\export.txt  -c -SKrishna  –Usa –Psa12'
Exec master..xp_cmdshell @st

I have tested with pubs database using the table name Employee.

Listing 4

Exec IMPEXPFROMDB ‘C:\EXP\export.txt’, ‘OUT’, ‘Employee’

A file will be created under EXP directory in the server as export.txt in which the data will be copied.  The file will be created in the server (where Sqlserver runs) not in the client.

I have tested with pubs database.

Listing 5

Exec IMPEXPFROMDB ‘C:\IMP\Import.txt’, ‘IN, ‘Employee’

The file should exist in the server in the valid directory.  The file should contain the default format as tab space for field separator and new line for row terminator.

You need to pass three parameters, a valid file path, direction either IN or out, and a valid table name or view name.  Using these parameters, a BCP command is built as a dynamic sql as pass to extended stored procedure xp_cmdshell.

Listing 6

Create procedure dbo.IMPEXPFROMDB
@filepath   varchar(255), --filepath
@direction  varchar(5) ,  --direction(In/Out)
@tablename  varchar(255)  --Valid tablename/viewname  
as
begin
set nocount on
 
      Declare @cmd            varchar(1000)     
      Declare @dbname         varchar(1000)
 
      /* Validation for filepath */
      If (@filepath is null)
      begin
            Raiserror('Please enter the FilePath ',16,1)
            Return
      end   
      
      /* Validation for Direction  */
      If (@direction is null) or (@direction Not In('In','Out'))
      begin
            Raiserror('Please enter the Direction(In/Out)',16,1)
            Return
      end
 
 
      /* Validation for Table name */
      If (@tablename is null)
      begin
            Raiserror('Please enter the Tablename or Viewname',16,1)
            Return
      end
      else if (Object_id(@tablename)is null)
      begin
            Raiserror('Please enter a Validate Tablename or Viewname',16,1)
            Return
      end
 
 
      --Get the database name.
      set @dbname = db_name()
      
--bcp command.
set @cmd = 'bcp  '+@dbname+'..'+@tablename+' '+@direction+'  '+@filepath+' -c -S -U -P'
 
 
      --To run the bcp using extended stored procedure.
      exec master..xp_cmdshell @cmd
 
set nocount off
end

Using this article you can easily import and export from a text file.  This is an alternative way, instead of DTS, when you need to copy a large amount of data from table or view.