Export All Tables from Microsoft SQL Server Database in Flat File format (csv, txt) at one Go !!

• Download sample - 2.1 KB

Introduction

In the era of the Cloud technology, very often, the need arises to export data in the flat file formats (CSV or txt) on a shared drive (AWS S3 bucket or Azure BLOB storage) and then use these files in ETL/ ELT for further processing on the cloud.

Let us enjoy learning an easy way of exporting all tables of the database to flat files !!.

Background

Microsoft SQL Server Export Data wizard allows one table at a time to be exported into a flat file, so to export all tables manually one by one using data export wizard will be a very time consuming and tedious task!!

Let us figure out answers to a few questions to export all the tables automatically into flat file format:

• What is the optimal way to export data when our data is very large?
• How can we do this task automatically?
• How can we export all the tables at one go into the flat files??

Using the Code

Let us answer our above-mentioned questions one by one!!

We will use BCP (Bulk Copy Program) utility, I hope you are familiar with this utility, if not then let me give you a brief idea!!

BCP utility helps for performing bulk copy operation of data between an instance of Microsoft SQL Server and a data file in a user-specified format. It can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into the data files.

Discussion on BCP is not the goal of this article. To read further and download BCP utility, Please visit Microsoft site.

We will use T-SQL Cursor to prepare script and run BCP commands for each table of the selected database and execute command using Windows command shell xp_cmdshell from SQL Server Management Studio (SSMS) to create flat files in our target folder where flat file is expected to be created.

Pre-Requisites

• Microsoft SQL Server (2012 onwards)
• BCP utility latest version
• Admin access to SQL server (Good to have)

I have used SQL server 2017 and AdventureWorksDW in my environment for testing.

Let us start understanding bits and pieces of our code !!

Step 1: Enable to use xp_cmdshell in MS. SQL Server

To enable xp_cmdshell in our SQL Server, your SQL user should have sufficient permission to execute these commands!! You can read further about configuration on the Microsoft site.

The below code can be run in SSMS post connecting to your Microsoft SQL Server instance with a user who should have sufficient permission to run this code.

Code snippet

-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

Step 2: Check Existence of the BCP Utility on Your System

Open Command prompt (Windows --> Run -->type CMD --> Click OK) and run the below command.

Code snippet:

C:\Users\xyz> bcp  /? 

If you can see the help on bcp utility, it means it is installed and ready to use or else, you need to download and install BCP utility from Microsoft website.

Alternate way you can check below location for existence of BCP application.

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

Step 3: BCP Command

Let us quickly understand BCP command preparation and its usage in our script.

Select query is tying column headers of character data with query resultset from the table using union all. Type casting is done to combine two different data type of column header with table rows. This will help us to write table data with table headers in flat file.

Code snippet:

Exec master..xp_cmdshell  'bcp "SELECT  ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout  "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U cybage\mohmmedmubins -t^| -T -c -d AdventureWorksDW'

Syntax

bcp "Select Query" queryout "OutPutFilePath"
- S  InstanceName
-e "ErrorFilePath"
-U domain\UserName
-t"Seperator"  e.g.  -t"," or -t"|"  etc.
-T  is for trusted connection,when used it requires to specify network user id and password
-c  uses char as the storage type,with \t (tab) 
    as the field separator and \r\n (newline) as the row terminator
-d DatabaseName  Specifies the database to connect 

Step 4: Dynamic Query Preparation

Breaking large script into smaller pieces of code snippet for better explanation, let us understand various SQL queries used in our code and its contribution.

Code snippet:

The following query will help us to get all the names of tables and relevant schema id when we run it in SSMS post selecting a database, e.g., in our case, AdventureWorksDW.

SELECT DISTINCT  st.NAME,st.schema_id  FROM sys.tables st where is_ms_shipped !=1;

Prepare Column Header row using selected table.

Code snippet: Header Row

The following query will help us to concatenate name of all columns, which will be written as a first row during export of the data to flat file.

DECLARE @columnHeader   VARCHAR(max)
SELECT  @columnHeader = COALESCE(@columnHeader+',' ,'') 
                        + '''' +'[' +column_name +']' +''''  
                        FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_NAME = 'DimAccount'
Print @columnHeader

Prepare Resultset using Column list of the selected table.

Code snippet: ResultSet

The following query will help us to combine all the names of table columns and type cast them to NVARCHAR so we can easily combine them with table header row which we have prepared earlier in the above code snippet.

DECLARE @ColumnList   VARCHAR(max)
SELECT  @ColumnList = COALESCE(@ColumnList+',' ,'')+
                     'CAST(['+column_name +'] AS NVARCHAR(max)) as [' + column_name +']'
                      FROM INFORMATION_SCHEMA.COLUMNS 
                      WHERE TABLE_NAME = 'DimAccount'
Print   @ColumnList

Combining table header Row with Resultset and prepare the final query.

Code snippet: FinalQuery

The following script will help us to combine table header row with table resultset. External type cast of resultset is done to NVARCHAR to easily combine table header row with table resultset and avoid any datatype mismatch issue during union all operation.

DECLARE @ColumnList             VARCHAR(max)  =''
DECLARE @columnHeaderCmdShell   VARCHAR(max)  =''
DECLARE @tempRaw_sql_xpcmdshell NVARCHAR(max) =''

SELECT  @ColumnList =         COALESCE(@ColumnList+',' ,'')+
                             'CAST(['+column_name +'] _
                              AS NVARCHAR(max)) as [' + column_name +']'
                              FROM INFORMATION_SCHEMA.COLUMNS 
                              WHERE TABLE_NAME = 'DimAccount'

SELECT  @columnHeaderCmdShell= COALESCE(@columnHeaderCmdShell+',' ,'') 
                               + '''' +'''[' +column_name +']''' +''''  
                               FROM INFORMATION_SCHEMA.COLUMNS 
                               WHERE TABLE_NAME = 'DimAccount'

SELECT  @tempRaw_sql_xpcmdshell= 'SELECT  '+  
                                SUBSTRING(@columnHeaderCmdShell, _
                                2, len(@columnHeaderCmdShell)) 
                               +' UNION ALL SELECT '+_
                                 SUBSTRING(@ColumnList, 2, len(@ColumnList))
                               +' FROM '  + '[dbo].[DimAccount]' 
--Print   @columnHeaderCmdShell
  Print   @tempRaw_sql_xpcmdshell

Prepare BCP command using final query and various parameters.

Code snippet: Prepare and Run BCP Command

We have to just concatenate final select query what we have prepared above and several other parameters expected by BCP command using correct concatenation syntax.

Finally, we can run our command using windows shell utility xp_cmdshell.

DECLARE @query_xpcmdshell varchar(8000)
DECLARE @FilePath VARCHAR(1000)='D:\Data\' 
DECLARE @ComputerName VARCHAR(1000) ='mohmmedmubins-w10' --instance name
DECLARE @UserName VARCHAR(1000)='domainname\mohmmedmubins' --user name

SET @query_xpcmdshell= 'bcp "' + @tempRaw_sql_xpcmdshell+'" queryout "'
                        + @FilePath +  @Filename+'.csv"'+' -S  ' +@ComputerName 
                        +' -e  "' + @FilePath +'error.log"'
                        +'  -U '+ @UserName 
                        +' -t, -T -c -d ' +DB_NAME() +''

Exec master..xp_cmdshell  @query_xpcmdshell

Step 4: Final Script

You can download and run the script post modifying few values like user name or instance name or separator or type of file you like to export.

Code snippet:

For comma separated CSV file -t^,

Exec master..xp_cmdshell  'bcp "SELECT  ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout  "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U "cybage\mohmmedmubins" -t^, -T -c -d AdventureWorksDW'

For pipe | separated CSV file -t^|

Exec master..xp_cmdshell  'bcp "SELECT  ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout  "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U "cybage\mohmmedmubins" -t^| -T -c -d AdventureWorksDW'

For comma, separated TXT file, requires change in file extension as .txt.

EXEC xp_cmdshell 'bcp "SELECT CountryRegionCode, _
Name FROM [AdventureWorks].[Person].[CountryRegion]" _
queryout "D:\data\CountryRegion.txt" -T -c -t, -S mohmmedmubins-w10\mssql2017'

This article and final script attached will give you a quick start, feel free to use, enhance and extend code as per your database and need.

Hope you enjoyed!! Don't forget to vote for efforts we spend to ease your life.

History

• 15^th July, 2020: Initial version