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

Topics

Newsletter Signup!



Follow us

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

Recent SQL Server Tips

Manning -->

-->