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

Views: 12.026
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.

Many times we need to export data easily to a text file and I've seen several desperate developers or DBAs because they don't know how to do it 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

In this screen, you must choose the destination source of the data. For file, I chose “Flat File Destination”. We can define where the data file will be written, character encoding (ISO-8859, UTF-8, etc.), the format of the data in the file (delimited by some character, fixed size or right-aligned), the text qualifier (eg if you set quotation marks as a qualifier, your password will look something like: “Dirceu”; ”29 ″;” DBA ”) and set whether the first row 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 commonly used by DBAs, as it is simple, it is already installed with SQL Server and can be executed in both 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

Where:
- out and queryout let you define how to export data. OUT exports an object and QUERYOUT the result of a query.
- -c defines that all fields will be exported as character (string)
- -t; lets you set the field separator, not limited to just 1 character as separator. In the first example, I am using the “;” as a column separator.
- -T is to inform you 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 for informing the server \ instance you want to connect to.

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

Example of use:

SQL Server - Export data to CSV with CLR (C #) Results

That's it folks!
Until the next post.

sql server export data text file txt export data text files from database database

sql server export data text file txt export data text files from database database