Export table contents to a CSV file from SQL Server
It may be useful to export a tables contents to a CSV file for some exploratory analysis, to feed an ETL and much more. This can be done with some simple SQL commands that exist as a stored procedure, SQL agent job or just a one off piece of code. The export can be the whole table or filtered results but one thing to keep in mind is the number of rows and result size as it may become difficult to open the resultant CSV file – for example, Excel has a 1 048 576 row limit. Therefore, to avoid such problems, it is advised that you develop the following code into a batch, possibly implementing a loop to act as a filter in order to reduce file sizes for accessibility.
The process will require the following elements for building the query:
- Table ( [table1] / [export table] / [source table])
- Destination folder & filename ([ C:\#testfolder\Output\table1_startdate_to_enddate ])
- Server name ([ testserver | @@SERVERNAME ])
- (Filters) ([ Date ] )
- (Variables) ([ @StartDate, @EndDate, @QueryDate, @FinalDate, @CMD ])
If you are interested in batching the export of data, the following is a basic loop over dates in a table that exports one month at a time. It also includes a truncate at the end to minimize the table size as it eventually handles many TB of data – if batching is not important then skip to ‘No Batching’.
Batched Approach:
DECLARE @QueryDate date –The date we’re exporting
DECLARE @FinalDate date –The maximum date to export
DECLARE @StartDate varchar(8) –To include in the export filename
DECLARE @EndDate varchar(8) –To include in the export filename
DECLARE @CMD varchar(8000) –The BCP command to execute
–Find the min/max dates to export
SELECT @QueryDate = MIN([date]) FROM [table1]
SELECT @FinalDate = MAX([date]) FROM [table1]
SET @StartDate = @QueryDate
WHILE @QueryDate <= @FinalDate
BEGIN
INSERT INTO [ export table ]
SELECT [ col1 ]
,[ col2 ]
,[ … ]
FROM [ table1 ] t
[INNER JOIN … ]
WHERE t.[Date] = @QueryDate
— Check for month elapsed
IF (datepart(day, dateadd(day, 1, @QueryDate)) = 1
BEGIN
— Get the start/end dates in a varchar, to use in the filename
SET @StartDate = convert(varchar(6), @QueryDate, 112) + ’01’
SET @EndDate = convert(varchar(8), @QueryDate, 112)
SET @CMD =
‘[ export command from below ]’
EXECUTE xp_cmdShell @CMD
TRUNCATE TABLE [ export table ]
END
— increment counter variable by 1 day
SET @QueryDate = dateadd(day, 1, @QueryDate)
END
Batching may be required if:
- Query is complex involving JOINs, CAST, aggregations and specific columns
- Retain history of exported data in newer structure
To retain the history of the data, we would remove the TRUNCATE statement, keep the staging table and add a WHERE clause in the BCP command to allow it to be a repeatable process(further below).
Now, for the important bit, the BCP export. So going back to our list of elements, I have set each one an alias to make the export command that bit easier to replicate.
Note: the apostrophes and double quotations are important so make sure you use the same arrangement. Also, queryout is important as it distinguishes between the source query and destination.
SET @CMD =
‘bcp.exe “SELECT * FROM [export table]” queryout ” C:\#testfolder\Output\table1_’ + @StartDate + ‘_to_‘ + @EndDate + ‘.csv” -c -t”,” -T -S [testserver]’
EXECUTE xp_cmdShell @CMD
If you do not know the server you wish to save the file on, you can run SELECT @@SERVERNAME to find out and hardcode it into the export command, otherwise you can just use @@SERVERNAME instead for variability. The command also allows for specific columns to be selected, just change the * accordingly.
No Batching
If batching is not important, nor retaining the history or dealing with a complex query, then you can run a similar command to go straight from the source data to file:
SET @CMD =
‘bcp.exe “SELECT * FROM [source table] WHERE [date] <= 20230301″ queryout ” C:\#testfolder\Output\table1_datestamp.csv” -c -t”,” -T -S [testserver]’
EXECUTE xp_cmdShell @CMD
More to learn:
You can find more information about the trailing letters of the BCP command here.
I might even be valuable to learn the different delimiters that can separate out columns – here.
Output
When running the export, you should see the following output – this will only appear once the export command begins to execute, there will no output during the loop.
