how to export sql data to csv using bcp

Question

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 this code export all my table, but i need only some fields and conditions like:

declare @sql varchar(8000) 
select @sql = 'bcp 
SELECT 

      ,[vl]
      ,[data]
      ,[URL]
      ,[parse]
      ,[Strata]
      ,[Id]
   FROM [ExcelAnalysis].[dbo].[ClearDB] where [data]> "01.05.2017" and NOT [vl] ="mag"and NOT [vl] ="Maxximo"   out c:\csv\status.txt -c -t, -T -S '+ @@servername 
exec master..xp_cmdshell @sql

but if i use any fields, the bcd returns message with his syntax. How do correct?

TylerH · Accepted Answer · 2024-05-28 20:56:07Z

10

First: Create a view in database

use [ExcelAnalysis].
go
;

create view [dbo].[vw_ClearDB] as
SELECT 

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

   GO

   ;

Second: Execute a statement to get the results into CSV

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

edited May 28, 2024 at 20:56

TylerH

21.3k84 gold badges84 silver badges122 bronze badges

answered Jun 17, 2017 at 16:47

Ven

2,0241 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Guzman Over a year ago

Enter the query directly as the first BCP parameter and `queryoutfor the second.

SimplyInk · Accepted Answer · 2018-06-28 07:50:25Z

6

Bcp queryout 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

edited Jun 28, 2018 at 7:50

SimplyInk

7,0521 gold badge21 silver badges28 bronze badges

answered Jul 19, 2017 at 16:41

Mukesh

839 bronze badges

Collectives™ on Stack Overflow

how to export sql data to csv using bcp

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related