未加星标

SQL Server export adhoc SQL or table to CSV file

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

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数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:SQL Server export adhoc SQL or table to CSV file
本站链接:https://www.codesec.net/view/628090.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(56)