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?

Create the query in a view and use that as DB object – Ven Jun 17 '17 at 16:39 — Ven, Jun 17 '17 at 16:39
could you show me? i don't know. i'm newby – psysky Jun 17 '17 at 16:41 — psysky, Jun 17 '17 at 16:41

Ven · Answer 1 · 2018-06-25T09:40:15.693

10

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
;

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

   ;


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 Jun 25 '18 at 09:40

answered Jun 17 '17 at 16:47

Ven

2,011
1
13
27

1

could you tell, i did like you, but error 'Message 102, level 15, state 1, procedure vw_ClearDB, line 29 Wrong syntax near the "GO" structure.' – psysky Jun 17 '17 at 17:19
1

Enter the query directly as the first BCP parameter and `queryoutfor the second. – Dan Guzman Jun 17 '17 at 21:16
1

For the view method, enclose string literals in single-quotes instead of double-quotes. – Dan Guzman Jun 17 '17 at 21:19
1

I got it. The semicolon must be before GO, after the query. Thnx.) – psysky Jun 18 '17 at 09:48
1

Hope its solved @D.Joe. Sorry have only seen your message now, Intellisense onthe server should solve itself – Ven Jun 18 '17 at 11:15

score 6 · Answer 2 · edited Jun 28 '18 at 07:50

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

how to export sql data to csv using bcp

2 Answers2

Linked

Related