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

In this article, we will see how to export SQL Server data to a Flat file using three different techniques. Before we proceed let’s setup our database.

Step 1: Create a database

Query:

CREATE DATABASE geeks;

Step 2: Select the newly created database

USE geeks;

Step 3: Table Definition

We have the following brands in our geeks database.

Query:

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

Step 4: Inserting records

Query:

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

Output:

Method 1: Saving Result to File via SSMS

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

Query:

SELECT * FROM brands;

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];

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:

Step 4: Result.txt file looks like this:

Method 2: Using Import/Export Wizard in SSMS

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

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:

If necessary, specify the 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:

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

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

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

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

Step 8: The Result.txt file will contain the 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.

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: