How to Export Records From SQL Server to Text File using BCP

How to Export Records From SQL Server to Text File using BCP

In this tutorial we will learn How to Export Records From SQL Server to Text File using BCP. Bulk Copy Program (BCP) is a command-line utility that ships with Microsoft SQL Server. With BCP, you can import and export large amounts of data in and out of SQL Server databases quickly and easily.

Steps:

1. Enable XP_CMDSHELL.

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

2. Use following query to export table data into text file.

EXEC xp_cmdshell 'bcp "SELECT uid, username, uemail FROM [dev].[dbo].[users]" queryout "C:\Temp\userlist.txt" -T -c -t,'