SQL Server export adhoc SQL or table to CSV file

In this post, let us see how to export adhoc sql query output or list of tables, views data into csv file. I am going to make use of R script to do this.

I have tried this example with SQL Server 2019 & WideWorldImporters sample database.

As a Pre-requisites, Install R services  and then from SSMS enable the external scripting feature. Restart the database engine and then verify the installation as mentioned in MSDN.

You might face “Access is denied” error while executing below scripts, to overcome that right-click on the folder where CSV files will be imported / exported and provide folder access (read, write) to "ALL_APPLICATION_PACKAGES".

For this approach, I have created a scalar function and Stored procedure.

Scalar function - returns a unique table name for a session. This function is required only if adhoc sql query has to be exported to csv file.

Stored procedure - Has below three input parameters and writes the output to csv file
i)   @ExportPath - path for exporting csv file
ii)  @ObjectlisttoExport - list of tables, views to be exported
iii) @Objectlistdelim  - delimiter for above object list



This script will work from SQL Server 2017 and above. Actually it can be made to work from SQL Server 2016 & above -  by modifying STRING_AGG that has been used inside the stored procedure to combine the list of columns into comma separated string. This approach will not work below SQL Server 2016 version as execution of R language using T-SQL was introduced in SQL Server 2016.


Scalar function:

USE [WideWorldImporters]
GO

CREATE OR ALTER FUNCTION fngetcsvtblname() 
RETURNS NVARCHAR(128)  
AS  
BEGIN  
     DECLARE @tblname NVARCHAR(128)     
     SELECT @tblname = CONCAT('ExportCSV_',@@SPID,DB_ID(),'_temptbl') 
  RETURN @tblname
END;  
GO  

Stored procedure:

CREATE OR ALTER PROC usp_ExportCSV (@ExportPath NVARCHAR(MAX),
                                    @ObjectlisttoExport NVARCHAR(MAX),
         @Objectlistdelim CHAR(1)
         )
AS
BEGIN
SET NOCOUNT ON;

IF ISNULL(@ExportPath,'') <> ''
 BEGIN

 SELECT @ExportPath = REPLACE(@ExportPath,'\','/')

 DECLARE @tblname NVARCHAR(128)
 SELECT @tblname= dbo.fngetcsvtblname() 

 IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tblname) OR (ISNULL(@ObjectlisttoExport,'') <> '' AND ISNULL(@Objectlistdelim,'') <> '')
 BEGIN

  
  DECLARE @TableColList TABLE (Cols NVARCHAR(MAX),Tbl NVARCHAR(128))

  IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tblname)
  BEGIN
  INSERT @TableColList
  SELECT  CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME)) Columns -- To cover poor data type conversions
      ,CONCAT(S.name,'.',O.name) TableName
  FROM sys.objects O
  JOIN sys.schemas S
  ON S.schema_id = O.schema_id
  JOIN sys.columns C
  ON O.object_id = C.object_id
  JOIN sys.types TY 
  ON C.[user_type_id] = TY.[user_type_id]
  WHERE CONCAT(S.name,'.',O.name) = (SELECT CONCAT(schema_name(schema_id),'.',name ) FROM sys.tables WHERE name = @tblname)
  AND TY.name <> 'image' -- Ignore the datatypes that are not required to be exported
  AND O.type IN ('U','V')
  END
  IF ISNULL(@ObjectlisttoExport,'') <> '' 
  BEGIN
  INSERT @TableColList
  SELECT  CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME)) Columns -- To cover poor data type conversions
      ,CONCAT(S.name,'.',O.name) TableName
  FROM sys.objects O
  JOIN sys.schemas S
  ON S.schema_id = O.schema_id
  JOIN sys.columns C
  ON O.object_id = C.object_id
  JOIN sys.types TY 
  ON C.[user_type_id] = TY.[user_type_id]
  WHERE CONCAT(S.name,'.',O.name) IN (SELECT value FROM STRING_SPLIT(@ObjectlisttoExport, @Objectlistdelim))
  AND TY.name <> 'image' -- Ignore the datatypes that are not required to be exported
  AND O.type IN ('U','V')
  END

  DROP TABLE IF EXISTS #ExportTablesList
  SELECT  IDENTITY(INT,1,1) Rn
  ,tbl
  ,STRING_AGG(cols,',') AS cols
  INTO #ExportTablesList
  FROM @TableColList
  GROUP BY tbl 

  DECLARE @I INT = 1
      ,@SQL NVARCHAR(MAX) = N''
      ,@RScript NVARCHAR(MAX) = N''
      ,@filename NVARCHAR(MAX) = N''
 
  WHILE @I <= (SELECT MAX(Rn) FROM #ExportTablesList)
  BEGIN
                --just for testing selecting top 10, this can be removed
    SELECT @SQL = CONCAT('SELECT TOP 10',Cols,' FROM ',tbl,';')
       ,@tblname = Tbl
    FROM #ExportTablesList WHERE Rn = @I


    SELECT @tblname = REPLACE(@tblname,'.','_')
    SELECT @filename = CASE WHEN SUBSTRING (@ExportPath,LEN(@ExportPath),1) = '/'  THEN  CONCAT(@ExportPath,@tblname,'.csv')
          ELSE CONCAT(@ExportPath,'/',@tblname,'.csv') END
 
    SET @RScript = CONCAT('write.csv(InputDataSet, file = "',@filename,'")')
    
    EXEC   sp_execute_external_script
       @language = N'R'
      ,@script = @RScript
      ,@input_data_1 = @SQL
 
    SET @tblname = ''
    SET @filename = ''
    SET @I = @I + 1
  END

  --After successful processing, dropping the table created for exporting adhoc sql into csv 
  SELECT @tblname= dbo.fngetcsvtblname() 
  IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tblname) 
  BEGIN
  EXEC('DROP TABLE '+@tblname)
  END

  END ELSE PRINT 'No object specified for exporting to CSV or Objectlist params are empty'

  END ELSE PRINT 'Export folder path need to be mentioned'

END;
GO

Let us see the different execution results:

1) When all the parameters are passed blank

2) When folder path is passed but no objects specified for exporting

3) Notice in the below screenshot, I am getting a unique table name using scalar function and inserting my adhoc sql output into that table and then calling the stored procedure.

This stored procedure exports the adhoc sql output to csv file in the mentioned path and drops the table created after successful processing.

4) I am passing the list of tables from Sales schema in WideWorldImporters database.
This stored procedure exports the list of tables data into separate csv files with table name as file name in the mentioned path.

5) This is to show that Stored procedure can export adhoc sql output, list of tables, views data into separate csv files simultaneously.

Sample execution code block:

SELECT dbo.fngetcsvtblname() --function to get unique table name for inserting adhoc sql into a table
DECLARE @tblname NVARCHAR(128) = dbo.fngetcsvtblname() 
  IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tblname) 
  BEGIN
  EXEC('DROP TABLE '+@tblname)
  END

/** place your adhoc sql to be exported into csv and SELECT INTO tablename provided by above function **/
SET NOCOUNT ON;
SELECT TOP 5 OL.OrderLineID,O.*
INTO ExportCSV_726_temptbl
FROM [WideWorldImporters].[Sales].[Orders] O
JOIN [Sales].[OrderLines] OL
ON O.OrderID = OL.OrderID

/** place your adhoc sql to be exported into csv and SELECT INTO tablename provided by above function **/

EXEC usp_ExportCSV 'H:\Temp\Blog\ImportExportExcel','SAles.Orders|Sales.Invoices|Website.Customers','|'

See Also:

• SQL Server Export/Import excel using R script
• My post on Technet wiki - SQL Server Import/Export to CSV using R script