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 Beginner;
Step 2: Select the newly created database
USE Beginner;
Step 3: Table Definition
We have the following brands in our Beginner 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: