How To Export Sql Data To Csv Using Bcp

I use simple code declare @sql varchar(8000) select @sql = 'bcp ExcelAnalysis.dbo.ClearDB out c:\csv\comm.txt -c -t, -T -S '+ @@servername exec master..xp_cmdshell @sql but th

Solution 1:

First Part : Create a view in database and second part to execute statement to get results into CSV.Let me know if you need more help

use [ExcelAnalysis].
go
;

createview [dbo].[vw_ClearDB] asSELECT 

      [vl]
      ,[data]
      ,[URL]
      ,[parse]
      ,[Strata]
      ,[Id]
   FROM [dbo].[ClearDB] where [data]> "01.05.2017" andNOT [vl] ='magand'NOT [vl] ='Maxximo'

   GO

   ;


declare@sqlvarchar(8000) 
select@sql='bcp ExcelAnalysis.dbo.vw_ClearDB out c:\csv\comm.txt -c -t, -T -S '+ @@servernameexec master..xp_cmdshell @sql

Solution 2:

Bcpqueryout option should be used.

Syntax would be:

SET@sql='bcp "SELECT [vl]
            ,[data]
            ,[URL]
            ,[parse]
            ,[Strata]
            ,[Id]
        FROM [dbo].[ClearDB] 
        WHERE [data] > ''01.05.2017''
        AND NOT [vl] =''mag''
        AND NOT [vl] =''Maxximo''" 
    queryout c:\csv\comm.txt
    -c -t, -T -S '+ @@servername+'\'+ @@servicename