代码区项目交易流程

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.
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
SQL Server export adhoc SQL or table to CSV file

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


SQL Server export adhoc SQL or table to CSV file

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.
SQL Server export adhoc SQL or table to CSV file

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.


SQL Server export adhoc SQL or table to CSV file

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


SQL Server export adhoc SQL or table to CSV file
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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

点击收藏

LAST Zeppelin-ms sql server interpreter SQL SERVER Which Settings Change in sp_configure Needs Restart to Take Effect ... NEXT