A generic way to export a table to csv.<\/p>\n\n\n\n
create table exportLog (\nid int identity(1,1) Primary key, \ntablename varchar(100), \nexportDate datetime2,\nexportColumn varchar (100),\nexportFrom varchar(200),\nexportTo varchar(200),\nexportRows int,\nexportJob varchar (100),\nexportFile varchar(300)\n)<\/code><\/pre>\n\n\n\n\n\/*\nProcedure exports a csv file with multiple variables:\nexport file name: @filename (e.g. export.csv)\npath:\t\t\t @exportPath (e.g. c:\\export)\nTable to export: @exportTable\nexport filters\nFilter rows based on one column: @exportColumn\nUse > and <=: @exportFrom,@exportTo\n*\/\ndeclare\n@serverName varchar (100),\n@exportTable varchar (50)='Shop.dbo.Test',\n@exportColumn varchar(100) ='c4',\n@exportColumnDataType varchar(50),\n@exportFrom varchar (100)='',\n@exportTo varchar(100)='',\n@cmdshellValue sql_variant,\n@exportPath varchar(500)='c:\\export',\n@filename varchar(100),\n@sql nvarchar (2000),\n@rows int ;\n\nBegin\n--Initialize Parameters\nselect @filename='Person_'+format(getdate(),'yyyyMMddHHmmss')+'.csv'\nselect @serverName=cast(serverproperty('ServerName') as varchar);\nselect @exportColumnDataType=t.name from sys.all_columns ac \njoin sys.types t on t.system_type_id=ac.system_type_id\nwhere object_id=OBJECT_ID(@exportTable) and ac.name=@exportColumn;\n\n\n\n---Check xp_cmdshell condition\nEXEC sp_configure 'show advanced options', 1\nRECONFIGURE WITH OVERRIDE;\nselect @cmdshellValue =value_in_use from sys.configurations where name ='xp_cmdshell';\nif @cmdshellValue=0 \nBegin\nEXEC sp_configure 'xp_cmdshell', 1\nRECONFIGURE WITH OVERRIDE;\nend;\n\n-------------------------------\n\/* When export column is set but From - To boundaries are not set then export uses max value loged in export log table as From and max current value on this column\n*\/\nif (@exportColumn!='ALL' AND (@exportFrom ='' or @exportTo =''))\nbegin\nif (@exportFrom ='' )\nbegin\nselect @exportFrom=max(exportTo) from exportLog where tablename=@exportTable;\nend;\nif (@exportTo ='')\nbegin\nif @exportColumnDataType='datetime'\nselect @sql= 'select @ExportTo=max(cast(cast ('+@exportColumn+' as datetime2) as varchar)) from '+@exportTable+';';\nelse\nselect @sql= 'select @ExportTo=max('+@exportColumn+') from '+@exportTable+';';\n\nprint @sql;\nexec sp_executesql @sql,N'@ExportTo varchar(100) output',@ExportTo OUTPUT;\nend;\nend;\n\n\n----Export\nif @exportColumnDataType='datetime'\nselect @sql= 'select @rows=count(*) from '+@exportTable+case when @exportColumn='ALL' then '' else ' where '+@exportColumn +' > cast('''+@exportFrom+''' as datetime2) AND '+@exportColumn + '<=cast('''+@exportTo+''' as datetime2) ' end +';'\nelse\nselect @sql= 'select @rows=count(*) from '+@exportTable+case when @exportColumn='ALL' then '' else ' where '+@exportColumn +' > '''+@exportFrom+''' AND '+@exportColumn + '<='''+@exportTo+'''' end +';'\nprint @sql;\nexec sp_executesql @sql,N'@rows int output',@rows OUTPUT;\nprint @rows\n\nif @exportColumnDataType='datetime'\nselect @sql='EXEC master..xp_cmdshell ''SQLCMD -S '+@serverName+' -E -Q "SET NOCOUNT ON; SELECT * FROM '+@exportTable+case when @exportColumn='ALL' then '' else ' where '+@exportColumn +' between cast ('''''+@exportFrom+'''''as datetime2) AND cast ('+''''''+@exportTo +'''''' end +' as datetime2); " -r 0 -b -o '+@exportPath+'\\'+@filename+'''';\nelse\nselect @sql='EXEC master..xp_cmdshell ''SQLCMD -S '+@serverName+' -E -Q "SET NOCOUNT ON; SELECT * FROM '+@exportTable+case when @exportColumn='ALL' then '' else ' where '+@exportColumn +' between '''''+@exportFrom+''''' AND '+''''''+@exportTo +'''''' end +'; " -r 0 -b -o '+@exportPath+'\\'+@filename+'''';\n\nprint @sql;\nexec sp_executesql @sql;\n\n\n\n----Log to table\ninsert into exportLog (tablename,exportDate,exportColumn,exportFrom, exportTo ,exportRows,exportJob, exportFile ) values (@exportTable,sysdatetime(),@exportColumn,@exportFrom,@exportTo,@rows,'Export '+@exportTable,@exportPath+'\\'+@filename)\n\n\nif @cmdshellValue=0 \nbegin\nEXEC sp_configure 'xp_cmdshell', 0;\nRECONFIGURE WITH OVERRIDE;\nend;\nEXEC sp_configure 'show advanced options', 0;\nRECONFIGURE WITH OVERRIDE;\nEnd;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"A generic way to export a table to csv.<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-85","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":6,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":103,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions\/103"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}