Export SQL Server Records into Individual Text Files

By:   |   Comments (2)   |   Related: More > Import and Export


There are times where you might want to export a table's records, each one into a separate text file. This for example can happen when you have text data in each record and you want to re-create the original files that were initially imported into the table that created these records.


In order to better understand the solution, let's consider the following scenario: Every day you run a process that imports a large set of text files (each file's content is imported as a single record in the database), into a SQL Server table in order be further processed. Additionally, there is a data archiving/cleaning process in place which deletes old files that were successfully imported to the database. Now, the scenario's requirement is to re-create certain original files by using as source the database table.

Letís see a simple example by using the below four text files:


This is text file 1
which contains
different lines of text
with code=1


This is text file 2
which contains
different lines of text
with code=2


This is text file 3
which contains
different lines of text
with code=3


This is text file 4
which contains
different lines of text
with code=4

Then we create a test database named "TestDB" as well as a table named "FileContents" for storing the above files contents:

CREATE TABLE [dbo].[FileContents]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [fileText] [text] NOT NULL, [code] [varchar](10) NOT NULL, [fileName] [varchar](50) NULL ); GO

After we import the four text files into the above table and run a SELECT statement against it, we get the below result:

FileContents table

Now let's consider that the above scenario features hundreds if not thousands of files which are no longer available and we want to re-create the original text files for records with codes equal to "code2" and "code3". To this end, what we want to do is to read the "FileContents" table, find the records we want based on the given condition, and export each one of these records into separate text files.

The algorithm for implementing the requirement is:

  1. Temporarily enable xp_cmdshell (you need this for running the BCP command)
  2. Create temporary table in TestDB database (column must have the same data type as in the original tableís column - in this case Text)
  3. Get the records (from table "FileContents") with codes "code2" and "code3" and store them into the cursor
  4. Iterate through the cursor values and for each value set do the following:
    • Delete all records from the temporary table
    • Insert the cursorís current value in the temporary table
    • Dynamically construct and execute the BCP command
  5. Disable xp_cmdshell

Now let's check the output directory before running the code. As you can see it is empty:

Output directory before export

The algorithm described above is translated to the below T-SQL script:

--Temporarily enable xp_cmdshell
sp_configure 'show advanced options',1;
sp_configure 'xp_cmdshell',1; RECONFIGURE GO
--Declare variables DECLARE @fileText VARCHAR(MAX); DECLARE @fileName VARCHAR(500) ; DECLARE @command varchar(1000);
-- --Declare parameters -- --Here you need to specify the output directory for the files to be created DECLARE @outputDir VARCHAR(250)='c:\tmp\files\';
--Here you need to specify the SQL instance from which the data will be exported DECLARE @instanceName VARCHAR(50)='.\SQL2K14'
--Here you specify whether you are connecting to the SQL instance with a --trusted connection (Windows Authentication) or not DECLARE @isTrustedConnection BIT=1
--If isTrustedConnection is set to 0 then you will need to --add username and password for connecting to the SQL Server instance DECLARE @userName VARCHAR(50)='loginName' DECLARE @password VARCHAR(50)='password'
--Create temporary table if it does not exist IF OBJECT_ID('TestDB..tmpSource') IS NULL CREATE TABLE TestDB..tmpSource( [fileText] [TEXT] NULL );
--Declare cursor and populate with data DECLARE db_cursor CURSOR FOR SELECT [fileText], [fileName] FROM TestDB..FileContents WHERE [code] IN ('code2', 'code3');
--Open cursor OPEN db_cursor FETCH NEXT FROM db_cursor INTO @fileText, @fileName
WHILE @@FETCH_STATUS = 0 BEGIN --Clean temporary table DELETE FROM TestDB..tmpSource
--Insert record into temporary table INSERT INTO TestDB..tmpSource (fileText) VALUES(@fileText);
--Dynamically construct the BCP command --If the user connects to the SQL instance using a trusted connection IF (@isTrustedConnection=1) SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -T -c' --If the user connects to the SQL instance using username/password ELSE SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -c -U ' + @userName + ' -P '+ @password
--Execute the BCP command EXEC xp_cmdshell @command, no_output
--Fetch next records FETCH NEXT FROM db_cursor INTO @fileText, @fileName END
--Close and deallocate cursor CLOSE db_cursor DEALLOCATE db_cursor GO
--Disable xp_cmdshell sp_configure 'xp_cmdshell',0 RECONFIGURE GO
sp_configure 'show advanced options',0 RECONFIGURE GO

The output we get after running the above script is illustrated in the below screenshot:

Code execution output

Now, if we check the output directory we can see that the two text files were reconstructed based on the relevant records in the "FileContents" table in the "TestDB" database:

Output directory after export

Also, if we check the contents of the two files we can see that they are exactly the same with the original files which were initially imported into the database table:

Reconstructed File 2

Reconstructed File 3
Next Steps

Review the following tips and other resources:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 8, 2016 - 2:14:11 AM - Artemakis Artemiou Back To Top (43719)

Hi Tim,

Thank you for your comment. 

For this article's scenario, as you can see, you need to loop over records in order to dynamically generate a text file for each one of the selected records.

Cursors is not the only -and maybe not the fastest- way to achieve this. By the time you can achieve looping functionality in order to iterate through the records, you can use other approaches as well. For example, you could use a temp table along with a WHILE loop, etc.




Monday, November 7, 2016 - 4:44:58 PM - Tim U Back To Top (43716)

 Hi Artemakis Artemiou,  I was reading your blog today on exporting data, and I wondered if you considered creating a set of distinct views instead of using a cursor?  This would allow you to avoid the single row processing that can impede the performance of cursors and reduce the amount of code you would need to write.




get free sql tips
agree to terms