SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Export records from SQL Server to Text File using BCP Command

Introduction

Is there a quick way to export records from SQL Server to text file? In this tip we will take a look at an example to export records from SQL Server to text file using BCP. In this tip we will learn how to Import and Export Bulk Data Using bcp Utility in SQL Server.

Solution

The fastest way to export records form SQL Server table to a text file is to use BCP command. Let us go through a live example of exporting data from [Person].[CountryRegion] table of AdventureWorks2012 database.

Let us go through the steps one by one to export records from SQL Server to text file.

Enable XP_CMDSHELL Using SP_CONFIGURE

First step will be to enable XP_CMDSHELL. I would recommend you to take a look at the following article to learn how to enable and disable XP_CMDSHELL “How to Enable and Disable XP_CMDSHELL using SP_CONFIGURE”. However, after use don’t forget to disable XP_CMDSHELL to avoid miss use.

If XP_CMDSHELL is not enabled you would end up receiving the below mentioned error message:

Error Message

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Once XP_CMDSHELL is enabled you can export the records from SQL Server table to text file using BCP commands.

Related Articles

Export records from SQL Server table to text file using BCP

Copy and paste the below sample command onto a new query window to export the results from [AdventureWorks2012].[Person].[CountryRegion] to a comma delimited file called CountryRegion.txt

BCP Command Line Utility to Export Data on a Default Instance

EXEC xp_cmdshell 'bcp "SELECT CountryRegionCode, Name FROM [AdventureWorks2012].[Person].[CountryRegion]" queryout "C:\Temp\CountryRegion.txt" -T -c -t,'

BCP Command Line Utility to Export Data on a Named Instance of SQL Server

EXEC xp_cmdshell 'bcp "SELECT CountryRegionCode, Name FROM [AdventureWorks2012].[Person].[CountryRegion]" queryout "C:\Temp\CountryRegion.txt" -T -c -t, -S MyTechMantra\SQL2012'

Important Parameters in BCP Command

  • Queryout: – this option allows one to specify the query to export.
  • File Name: – Specify the file name after queryout parameter where the query results will be stored.
  • -T parameter specifies that BCP utility will use trusted connection using integrated security to connect with SQL Server. If you wish to use a SQL Server Login then specify –U and –P
  • -c parameter specifies that character data type will be used for each field.
  • -t parameter allows one to specify a field delimiter. To specify comma (“,”) as field delimiter for data fields specify it as (-t,)
  • -S parameter can be used to specify server name. For a named instance specify it as –S [SERVERNAME\INSTANCENAME].

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement