Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to export database data to text file (CLR, OLE, BCP)

Reading Time: 14 minutes

Hello people,
Good night!

After writing my previous post, where I talked about How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)I ended up getting excited to write the 2 part, which is the reverse process, that is, exporting the data from SQL Server to a text file, which is exactly this post.

We often need to export data easily to a text file and I have seen several developers or DBA's desperate because they don't know how to do this efficiently.

Let's settle this once and for all.

Exporting from SQL Server Management Studio

How to export data through SQL Server Management Studio

Undoubtedly, the simplest and easiest way to export data to file in SQL Server is by using the tool's own interface, which is Management Studio.

In the Object Explorer screen, right-click on the database containing the tables you want to export and select the option "Tasks" -> "Export Data ..."
SQL Server - Export data to text file with Management Studio

In this screen you should choose the source of the data source, configure the connection data and the database you will export.
SQL Server - Export Data to Text File with Management Studio 2

On this screen, you must choose the destination source of the data. For file, I chose “Flat File Destination”. We can define where the file will be recorded with the data, character encoding (ISO-8859, UTF-8, etc.), the format of the data in the file (delimited by some character, fixed size or aligned to the right), the text qualifier (ex: if you define the quotation marks as a qualifier, your password will be something like this: "Dirceu"; "29 ″;" DBA ") and define whether the first line will contain the table header.
SQL Server - Export Data to Text File with Management Studio 3

In this screen you can define whether you want to select the objects you want to export (first option) or you want to write a query and the query result will be exported to the file. For this example, I will choose the first option.
SQL Server - Export Data to Text File with Management Studio 4

Here you can choose the object to export and set the line break format (Windows = CRLF, UNIX = LF) and the column delimiter character.
SQL Server - Export Data to Text File with Management Studio 5

Finally, on this screen you define whether you already want to export data (Run immediately) and whether you want to generate an Integration Services (SSIS) package if you want to automate this task as a Job by SQL Agent or run it manually without having to configure everything. again.
SQL Server - Export Data to Text File with Management Studio 6

SQL Server - Export Data to Text File with Management Studio 7

SQL Server - Export Data to Text File with Management Studio 8

Although this is simple, to export multiple tables it is laborious and impractical. For this reason, I will explain below how to do this via T-SQL.

Exporting by BCP (Bulk Copy)

How to export SQL Server data to txt file with BCP

This is one of the options most used by DBA's, because it is simple, it is already installed with SQL Server and can be executed both in SSIS packages and stored procedures (using xp_cmdshell)

Remember that to use BCP, you will need to enable the xp_cmdshell feature. I particularly don't like to use xp_cmdshell or leave it enabled in one instance as it allows for numerous vulnerabilities and any Windows Prompt command can be run with this feature enabled.

To enable the feature and enable xp_cmdshell, run the following commands:

Example of use:

SQL Server - Export data to CSV with BCP out queryout Results

- out and queryout allow you to define how to export the data. OUT exports an object and QUERYOUT the result of a query.
- -c defines that all fields will be exported as a character (string)
- -t; allows you to define the separator of the fields, not limited to just 1 character as a separator. In the first example, I am using the “;” as a column separator.
- -T is used to inform that the connection will be made in Trusted Connection mode (Windows Authentication). If you want to use SQL Server authentication, just use -User and -Password.
- -S is used to inform the server \ instance that you want to connect.

BCP Parameter Definitions:
BCP Help

Exporting with OLE Automation

How to export text files to bank with OLE Automation

For those unfamiliar with this feature, it allows the DBA or Developer to perform a series of actions on the database using OLE DB, such as read / write / move / copy / delete files, Excel spreadsheet creation and more things. The syntax is somewhat similar to VBA and uses the Windows API for these operations.

For this purpose, we will use the Stored Procedures stpWrite_File_Store and SaveDelimitedColumns, as shown below:

Procedure source code stpWrite_File_File

SaveDelimitedColumns Procedure Source Code
To view the source code of this procedure, created by John Buoro, go to this link or below:

Example of use:

SQL Server - Export data to text file with OLE Automation 2

Exporting with common language runtime (CLR)

How to export text files to bank with CLR

The CLR allows you to be able to create routines (stored prodecures, functions, triggers, etc.) written in C #, F # and VB.NET, compile and execute them in the database natively, extending DBMS capabilities, as it is possible. create a multitude of things that would not be possible using just Transact-SQL, such as file manipulation, FTP file upload and download, aggregate functions, Webservices integration, and more.

For this purpose, we will use Stored Procedures stpExporta_Query_Txt and stpWrite_File, as shown below:

StpExporta_Query_Txt procedure source code

Procedure source code stpWrite_Archive