Export Data to CSV (SQL Server)

This article will go through steps to Execute SQL command or Stored Procedure and Export Data to CSV file. These Steps can be automated using SQL Server Agent Job.

Follow link to see SQL Server Agent Job creation Steps

Installing AWS CLI

Step 1:

• Create new SQL Server Agent Job
• Add First Step to Delete old files from Export Location

EXEC master..xp_cmdshell 'del C:\Users\Public\DataExports\Product_*.csv'

Step 2:

Add 2nd Step to SQL Agent Job that will Run Query or Stored Procedure and Export Data to CSV in a local Folder

DECLARE @FileName NVARCHAR(100)
DECLARE @RepDate NVARCHAR(10) 

SELECT   @RepDate = CONVERT(varchar(8), GETDATE() ,112)


SELECT 
	@FileName = 'Product_' + @RepDate + '.csv'

PRINT @FileName

DECLARE @SQL nvarchar(800)
SELECT 
 	@SQL = 'bcp "EXEC [Sales]..[Product]" queryout "C:\Users\Public\DataExports\' + @FileName + '" -S ' + @@SERVERNAME + ' -T -c -C 65001 -t"|"'

EXEC master..xp_cmdshell @SQL

Step 3:

Add 3rd Step in SQL Agent Job to Push data to AWS S3 Bucket. BAT file referenced in below command needs to be placed on SQL Server. This BAT File will have S3 Target Location and Profile Information.

Job Type: Operating System (Cmd Exec)

cmd.exe /c "C:\Users\Public\DataExports\S3AWSCLI-Product.bat > C:\Users\Public\DataExports\log_Product.txt"

BAT File Content (S3AWSCLI-Product.bat)

set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%

aws s3 cp C:\Users\Public\DataExports\Product_%CUR_YYYY%%CUR_MM%%CUR_DD%.csv s3://s3-folder/sales/ --profile S3AWS --acl bucket-owner-full-control 

Create AWS Profile

Other Useful Links

Call API End Point from SQL Server Stored Procedure – Simplyfies

Database Interview Question and Answers (Part-1) – Simplyfies

Choosing Right Database for Application – Simplyfies