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

• Different ways to retrieve System and SQL Server Information
• How to configure SQL Server Agent to Restart SQL Server and SQL Server Agent Services Automatically
• How to connect to a named instance of SQL Server?
• Fix Index cannot be reorganized because page level locking is disabled error in SQL Server
• Grant Access to DMVs in SQL Server for Non Admin
• How to Improve SQL Server Replication Performance
• Unable to create or open SSIS projects or maintenance plans in SQL Server 2012
• Performance Dashboard Reports in SQL Server 2012
• How to Attach Database Without a Transaction Log File in SQL Server
• New Features in SQL Server 2012 Reporting Services for Developers
• New Features in SQL Server 2012 for Database Administrators
• New Features in SQL Server 2012 for Database Developers

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].