CODEWITHSUNDEEP
sql-serverexportssmsexport-to-csv
Liam neesan
Liam neesan

Reputation: 2571

How to export SQL data records to CSV format using stored procedure?

Is it possible to export records to csv format using SQL script in stored procedure?

I am trying make job schedule, that will export records into a .csv file. I am using SQL Server 2012.

I don't want to make small application for just an exporting. That's why I am trying to make a script and add it in schedule. For example, I have records like

EmpID  EmployeeName  TotalClasses 
---------------------------------
01     Zaraath        55
02     John Wick      97

File destination location is D:/ExportRecords/file.csv

Upvotes: 1

Views: 9369

Answers (2)

SteveC
SteveC

Reputation: 6015

You could use BCP (Bulk Copy Program) the built-in cli for exporting data from SQL Server. It's low overhead, executes fast, and has lots of feature switches, like -t (which creates CSV file) which make it good for scripting.

Something like this. The Docs are useful as well

BCP dbo.YourTable out D:/ExportRecords/file.csv -c -t

Upvotes: 0

Soundappan A
Soundappan A

Reputation: 331

In SSMS you can save the query result in CSV format.

enter image description here

Try This Below Query

-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO 

declare @sql varchar(8000)
select @sql = 'bcp "select * from DatabaseName..TableName" queryout d:\FileName.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

You have to create Empty FileName.csv in D:\

Upvotes: 0

Related Questions