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

Thursday, March 19, 2009 |

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

0 comments: