Working with Bulk Copy Program Utility
 
Published: 07 Aug 2006
Unedited - Community Contributed
Abstract
In this article you will learn how to import and export data using Bulk Copy Program utility.
by Krishnamoorthy D
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 34767/ 112

Introduction

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.

How it Works

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- Bulk copy

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
Export from a file to a table/view

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.

Import from a file to a table/view

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.

Stored Procedure to Import and Export

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 nullor (@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
Conclusion

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.



User Comments

Title: Chido one   
Name: Mario
Date: 2007-04-27 1:35:29 PM
Comment:
I read many webs, but this is easy.
Tks a lot , a lot, a lot.
Title: BCP Programming   
Name: sivakumar R
Date: 2006-12-26 7:56:35 AM
Comment:
Fine , Thanks a lot
Title: Example is wrong   
Name: Faiz
Date: 2006-12-11 10:19:41 PM
Comment:
Following Example wromg .
Example : if you like to copy from differant server
also SP displaying 2 times for table name.
Thanks
Title: Bulk article   
Name: Radhakanta
Date: 2006-11-14 3:09:37 AM
Comment:
nice article and is very useful.
Title: BCP Article   
Name: Siva Kumari K
Date: 2006-09-13 4:07:27 AM
Comment:
Very Usefull Article, Really i wasted lot time before, thank u very much
Title: BCP UTILITY   
Name: BALAJI SINGH
Date: 2006-08-15 11:45:25 AM
Comment:
Bulk made easy and understandable, with this simple article.

Thanks.
Title: Now we need a BCP Format article   
Name: Darren Bates
Date: 2006-08-11 1:18:25 PM
Comment:
Thanks for the overview. What I'm trying to work with is the format file to define the data import from a text (or native) file into a table where the schema doesn't exactly match
Title: Good   
Name: Manohar
Date: 2006-08-08 2:58:19 AM
Comment:
Good
Title: BCP Article   
Name: G.Balasubramanian
Date: 2006-08-08 12:40:03 AM
Comment:
Very usefull, automates lot of process, thanks..
Title: BCP Article   
Name: Aravind Kumar
Date: 2006-08-08 12:21:20 AM
Comment:
Very useful article. This will automate lot of process and save the worktime.

Aravind
Title: Bulk article   
Name: Esmeralda Pita
Date: 2006-08-07 12:05:39 PM
Comment:
Very usufull, thanks






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-24 5:49:19 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search