Bulk Insert – Bulk Copy or BCP (Difference and Comparison) in SQL Server 2005

Now I wrote quite a few articles on BULK COPY and BULK INSERT commands so I thought to give one separate article for differences between BULK INSERT and BULK COPY (BCP).

Let me start with its type. BULK INSERT is a SQL command and BCP is a separate utility outside SSMS and you need to run BCP from DOS prompt (command prompt).

BULK INSERT can copy data from flat file to SQL Server’s table whereas BCP is for import and export both. You can copy data from flat file to SQL Server and from SQL Server to Flat file with the help of BCP.

You can use INOUTQUERYOUT argument with BCP to import, export and conditional import and export which in not possible in BULK INSERT.

In BULK INSERT there is no support for down level version input whereas it is possible in BCP.

BCP has less parsing efforts and cost than BULK INSERT.

Apart from above differences, both are almost same and give almost same performance moreover, both are single threaded, no parallel operation allowed.

Reference: Ritesh Shah

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

Bulk Operation or BULK Insert in SQL Server 2005:

When you have big flat file may be CSV with lots of records may be couple hundred mega byte file and you wants to transfer those data into SQL-Server than you are at the right article as bulk insert is one of the way to get this kind of heavy lifting.

It is really very efficient and fast as it by passes the transaction and directly injects the data into data file. Let us try by creating one .CSV file (a.k.a. comma delimited file or comma separated value) and load it in to SQL Server database table.

Open your notepad.

— Copy following data in it. Below given are a dummy data and represent name, department and company name.

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

— Save the above notepad file as “emp.txt” in your “C” drive.

— Open your SSMS and create following table and use BULK INSERT command as follows:

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–BULK INSERT to insert file’s data to emps table

BULK INSERT emps

FROM ‘c:\emp.txt’

WITH (FIELDTERMINATOR=‘,’,FIRSTROW =0, ROWTERMINATOR=‘\n’);

–FIELD TERMINATOR defines the separator it could be any like ‘,’ or ” etc.

–FIRSTROW defines from where it should read data, first line of the file may be

–header so SQL shouldn’t isert it.

–ROWTERMINATOR is new line characte ‘\n’, it could be different if you have got the

–file from mainframe PC or other system it could be different, you can see it in hex editor

–test the data whether it has really been copied.

SELECT * FROM emps

So this is all about BULK INSERT. It is really very fast. Once I did BULK INSERT for about 100,000 records and it hardly took 2 minutes. Enjoy bulk operation!!!!

Reference: Ritesh Shah