SQL Server - Append Query Result to .csv file

While searching on the web, I found a lot talking about appending SQL Server data to Excel files. But not many about appending data to .csv file. After some research, I got one.

First, create the csv file and prepare the column header:

master..xp_cmdshell 'echo EmpID,LastName,FirstName > C:\temp\Employees.csv'

It creates a file c:\temp\Employees.csv with 3 columns.

Then, append the data using OPENROWSET:

INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp',
[Employees#csv])
select EmployeeID, LastName, FirstName from NOrthwind..Employees
(9 row(s) affected)

Open the file c:\temp\Employees.csv, you'll see there are 9 rows appended.
Some points here are: 1) Database=C:\temp (need to put the folder here only), and 2) [Employees#csv] (need to replace . with # as the table name.

OK, it's similar to read the file back:

select * from OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp', [Employees#csv])

1 Davolio Nancy
...
9 Dodsworth Anne

At the end, I'll show another way to read the data in csv file:

select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp;',
'select top 3 * from "Employees.csv"')

It seems you cannot append to csv using MSDASQL.