Open In App

How to Export SQL Server Data to a Text File Format?

Last Updated : 15 Jun, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

Exporting SQL Server data to a text file is a common task that is used in data migration, data sharing, etc. SQL Server provides several methods to export data to a text file format, including using the SQL Server Management Studio (SSMS), the SQL Server Command Line Tool (sqlcmd), and the SQL Server Integration Services (SSIS).

In this article, we will see how to export SQL Server data to a text file using these three techniques.

Before we proceed, let’s set up our database and table.

Query:

CREATE DATABASE geeks;
USE geeks;

CREATE TABLE brands(
brand_id INT PRIMARY KEY,
brand_name VARCHAR(30) NOT NULL
);

INSERT INTO brands
VALUES
(1, 'Electra'),
(2, 'Haro'),
(3, 'Heller'),
(4, 'Pure Cycles'),
(5, 'Ritchey'),
(6, 'Strider'),
(7, 'Sun Bicycles'),
(8, 'Surly'),
(9, 'Trek');

The table will created, and now we will export this table into text file.

How to Export Table Data to a Text File in SQL Server?

There are three methods to export the table data from SQL Server into a text file:

  • Saving Result to File via SSMS
  • Using Import/Export Wizard in SSMS
  • SQLCMD Utility

Let’s understand each of these methods in detail. We will cover each method and learn how to export table data to a text file in SQL Server with examples and step-by-step process.

Method 1: Saving Result to File via SSMS

Saving result to file via SSMS is a simple way to export table data to a text file in SQL Server. This method involves selecting the query results and then saving them to a file.

Let’s look at the steps for this method.

Step 1: First, let’s have a look at our brand’s table.

Query:

SELECT * FROM brands;

table

Step 2: Write down the query onto the editor whose output needs to be saved. If you want to save the results in a flat file, you can do this in SSMS. Right Click on Editor > Results to > Results to File:

Query:

SELECT TOP (1000)
[brand_id],
[brand_name]
FROM
[sample].[production].[brands];

results to file option

Step 3: Execute the query. An option to specify the name and path will be displayed. Change the type to All Files and Save it with the .txt extension:

save file

Step 4: Result.txt file looks like this:

text file saved

Method 2: Using Import/Export Wizard in SSMS

The SQL Server Import and Export Wizard is a tool in SQL Server Management Studio (SSMS) that allows users to copy data from one location to another. Using this tool, we can export the data to text file and save it for later use.

Let’s look at the steps to perform this method.

Step 1: When we right-click a database in SSMS. It is possible to import or export data. Navigate to Tasks>Export Data:

export data option

Step 2: The SQL Server Import and Export wizard will be launched. We will export from SQL Server to a Flat file. Select the SQL Server Native Client 11.0 as the Data Source:

SQL Server Native Client 11.0 option

If necessary, specify the Server name and connection information:

server name and connection information

Step 3: Select Flat File Destination from the destination drop-down menu and hit Browse to set the file name and path:

Flat File Destination

Step 4: The flat file name in our case would be Result.txt:

save Flat File

Step 5: Once we have determined the file name and path, proceed as follows:

chosse a destination window

Step 6: Choose “Copy data from one or more table or views” or select second option to specify our own query:

Copy data from one or more table or views option

Step 7: To export the data instantly, choose Run immediately:

export data instantly

Step 8: The Result.txt file will contain the output:

text file output

Method 3: SQLCMD Utility

The SQL Server Command Line tool is SQLCMD. This tool allows you to store the results in a file. When utilizing batch files to automate processes, this option comes in handy.

Let’s check the steps to use this method.

Step 1: Here’s how our SaveOutputToText.sql file look’s like:

Query:

SELECT TOP (1000)
[brand_id],
[brand_name]
FROM
[sample].[production].[brands];

Step 2: Use the following command on your terminal to save the results of any query onto file:

Query:

sqlcmd -i SaveOutputToText.sql -o Result.txt

Step 3: The Result.txt file contains the output:

text file

These were the best three methods to export data to text format in SQL Server. You can use any of the three methods provided as all of them are easy and effective.



Next Article
Article Tags :

Similar Reads