A generic way to export a table to csv.
create table exportLog (
id int identity(1,1) Primary key,
tablename varchar(100),
exportDate datetime2,
exportColumn varchar (100),
exportFrom varchar(200),
exportTo varchar(200),
exportRows int,
exportJob varchar (100),
exportFile varchar(300)
)
/*
Procedure exports a csv file with multiple variables:
export file name: @filename (e.g. export.csv)
path: @exportPath (e.g. c:\export)
Table to export: @exportTable
export filters
Filter rows based on one column: @exportColumn
Use > and <=: @exportFrom,@exportTo
*/
declare
@serverName varchar (100),
@exportTable varchar (50)='Shop.dbo.Test',
@exportColumn varchar(100) ='c4',
@exportColumnDataType varchar(50),
@exportFrom varchar (100)='',
@exportTo varchar(100)='',
@cmdshellValue sql_variant,
@exportPath varchar(500)='c:\export',
@filename varchar(100),
@sql nvarchar (2000),
@rows int ;
Begin
--Initialize Parameters
select @filename='Person_'+format(getdate(),'yyyyMMddHHmmss')+'.csv'
select @serverName=cast(serverproperty('ServerName') as varchar);
select @exportColumnDataType=t.name from sys.all_columns ac
join sys.types t on t.system_type_id=ac.system_type_id
where object_id=OBJECT_ID(@exportTable) and ac.name=@exportColumn;
---Check xp_cmdshell condition
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
select @cmdshellValue =value_in_use from sys.configurations where name ='xp_cmdshell';
if @cmdshellValue=0
Begin
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;
end;
-------------------------------
/* 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
*/
if (@exportColumn!='ALL' AND (@exportFrom ='' or @exportTo =''))
begin
if (@exportFrom ='' )
begin
select @exportFrom=max(exportTo) from exportLog where tablename=@exportTable;
end;
if (@exportTo ='')
begin
if @exportColumnDataType='datetime'
select @sql= 'select @ExportTo=max(cast(cast ('+@exportColumn+' as datetime2) as varchar)) from '+@exportTable+';';
else
select @sql= 'select @ExportTo=max('+@exportColumn+') from '+@exportTable+';';
print @sql;
exec sp_executesql @sql,N'@ExportTo varchar(100) output',@ExportTo OUTPUT;
end;
end;
----Export
if @exportColumnDataType='datetime'
select @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 +';'
else
select @sql= 'select @rows=count(*) from '+@exportTable+case when @exportColumn='ALL' then '' else ' where '+@exportColumn +' > '''+@exportFrom+''' AND '+@exportColumn + '<='''+@exportTo+'''' end +';'
print @sql;
exec sp_executesql @sql,N'@rows int output',@rows OUTPUT;
print @rows
if @exportColumnDataType='datetime'
select @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+'''';
else
select @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+'''';
print @sql;
exec sp_executesql @sql;
----Log to table
insert into exportLog (tablename,exportDate,exportColumn,exportFrom, exportTo ,exportRows,exportJob, exportFile ) values (@exportTable,sysdatetime(),@exportColumn,@exportFrom,@exportTo,@rows,'Export '+@exportTable,@exportPath+'\'+@filename)
if @cmdshellValue=0
begin
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;
end;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
End;