Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Export SQL Server Records into Individual Text Files


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

Problem

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.

Solution

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:

testFile1.txt

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

testFile2.txt

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

testFile3.txt

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

testFile4.txt

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 DATABASE TestDB;
GO
USE TestDB; GO
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;
RECONFIGURE
GO
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:



Last Update:






About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server Architect, Software Developer, and a Microsoft Data Platform MVP (SQL Server).

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, November 08, 2016 - 2:14:11 AM - Artemakis Artemiou Back To Top

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.

 

Cheers,

Artemakis


Monday, November 07, 2016 - 4:44:58 PM - Tim U Back To Top

 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.

 

 

 


Learn more about SQL Server tools