how to export sql data to csv using bcp
30,069
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
;
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
Solution 2
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
Author by
D.Joe
Updated on June 28, 2020Comments
-
D.Joe almost 4 years
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?
-
psysky almost 7 yearscould 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.'
-
Dan Guzman almost 7 yearsEnter the query directly as the first BCP parameter and `queryoutfor the second.
-
Dan Guzman almost 7 yearsFor the view method, enclose string literals in single-quotes instead of double-quotes.
-
psysky almost 7 yearsI got it. The semicolon must be before GO, after the query. Thnx.)
-
Ven almost 7 yearsHope its solved @D.Joe. Sorry have only seen your message now, Intellisense onthe server should solve itself