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


July 04, 2013

Problem

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.

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:-

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.

Newsletter Signup

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

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

Default Instance

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

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

Named Instance

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

Let us take a look some of the most important parameters which you will be using along 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].

Share this Article

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."