Working with Bulk Copy Program Utility
page 2 of 7
by Krishnamoorthy D
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 34042/ 94

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.

View Entire Article

User Comments

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

Title: Now we need a BCP Format article   
Name: Darren Bates
Date: 2006-08-11 1:18:25 PM
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
Title: BCP Article   
Name: G.Balasubramanian
Date: 2006-08-08 12:40:03 AM
Very usefull, automates lot of process, thanks..
Title: BCP Article   
Name: Aravind Kumar
Date: 2006-08-08 12:21:20 AM
Very useful article. This will automate lot of process and save the worktime.

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

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

©Copyright 1998-2020  |  Page Processed at 2020-08-09 1:47:32 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search