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

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

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-2021  |  Page Processed at 2021-07-26 1:24:44 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search