Create comma separated file from SQL Server 2008 with BCP and XP_CMDSHELL

I was assigned a task few days back to generate comma separated file from SQL Server table, It is very easy to do so with BCP utility. Generally we used to use BCP from DOS prompt but herewith I am going to use BCP right from my SSMS with XP_CMDSHELL.

 

use Adventureworks

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

 

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

 

 

declare @sql varchar(1000)

–creating emps.txt file in D drive from just created table above

select @sql = ‘bcp Adventureworks..emps out d:\emps.txt -c -t, -T -S ‘+ @@servername

–print @sql

–generally, you should run BCP from command prompt, if you wish to do it from

–SSMS, you have to use xp_cmdshell

exec master..xp_cmdshell @sql

 

 

If your xp_cmdshell is disable, there is one small article to enable it at:
http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html

I have written some more interesting article on BCP which you can find at
http://www.sqlhub.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
http://www.sqlhub.com/2009/03/bcp-export-data-from-sql-server-to-flat.html
http://www.sqlhub.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is 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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is 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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah