BCP or Bulk Copy Program in SQL Server 2005:

Bulk Copy Program (BCP) used to import and export data from command line. It is similar to Bulk Insert. You can refer my article about BULK INSERT in SQL Server at

http://ritesh-a-shah.blogspot.com/2009/03/bulk-operation-or-bulk-insert-in-sql.html

As BCP is command line variation of Bulk operations and an external program you will need authorization to connect to SQL Server.

Let us see it practically by creating one .TXT file for demo.

Ritesh,MIS,echem
Bihag,MIS,CT
Rajan,account,Marwadi
Alka,account,tata
Pinal,DBA,sqlautho
Alpesh,Chemical,echem

Save above records in one .TXT file in “C” drive. I named it “Emp.TXT” in “C” drive.

Once you save the file, you have to have one table in which you would like to transfer data from emp.txt file.

Let us create one blank table for that.

--create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

So now, you are ready to run your first BCP command. Open your command prompt. Start->Run->cmd

Once your command prompt is open. Type down following command there and press enter. (start typing from “BCP” not from “C:\>” ;) )

C:\> BCP adventureworks.dbo.emps in c:\emp.txt –T –c –t, -r \n

Once you run above command you will get message

6 rows copied
Network Packet Size (bytes) : 4096
Clock Time (ms.) Total : 1 Average : (6000.00) row per second.

Last two lines of message may be different based on system. Let us understand above given command.

BCP: run the BCP.EXE utility to import and export data.

Adventureworks.DBO.emps: database name, schema name and table name in which we wants to transfer data.

In: specify that we want to import data from .TXT to SQL table.

C:\emp.txt: path of my file from where I want to transfer data.

-T: its for trusted connection, windows authentication. If your login is not trusted than you should use –U sa(user name) –P sa (password of account)

-c: specifies that data will be loaded as character data.

-t,: field terminator (-t) shows that we will use “,” comma as field terminator.

-r \n: line terminator (-r) shows that we will consider “\n” as line terminator.

Hope you have enjoyed BCP command.

Reference: Ritesh Shah