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

Advertisements

BCP – Export data from SQL Server to Flat file (Bulk Copy Program)

I wrote few articles for BULK INSERT, BCP but in all of those articles I have imported data from flat file to SQL Server table. But now in this article I will show you how you can export data from SQL SERVER to flat file like TXT or CSV etc.

BTW, if you want to refer my past article for BULK INSERT and BCP, please follow below given link.

For BCP:
http://ritesh-a-shah.blogspot.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
For Bulk Insert:
http://ritesh-a-shah.blogspot.com/2009/03/bulk-operation-or-bulk-insert-in-sql.html
Now let us move to our core topic of the article.

I will create one table in SQL Server and insert some rows in it, which I will be transfer from SQL Server’s table to text file.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Pinal’,‘DBA’,‘sqlautho’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

Now let me open Command Prompt from where I can run BCP.EXE utility to export my data from SQL Server to Flat File.

Run following command in your Command Prompt (start typing command from BCP, not from “C:\>”)

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

That’s it, you are done. You will get one file name “empTmp.txt” in your “C” drive. For understanding argument of BCP command, please refer my previous article at:

http://ritesh-a-shah.blogspot.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html

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