Search code examples
sql-servert-sqlcsvopenrowset

How to change my T-SQL query to overwrite a csv file rather than append data to it?

I have the following T-SQL codes configured to run on a daily basis using SQL Server Agent job. My database is running on SQL Server 2012.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;HDR=YES;FMT=Delimited','SELECT * FROM [myfile.csv]')

SELECT ReservationStayID,NameTitle,FirstName,LastName,ArrivalDate,DepartureDate FROM [GuestNameInfo]

My issue is that the output of this query is being appended to the existing records in the csv file. I want the output to overwrite the existing content each time the SQL Server Agent job is run.

How do I modify my query to acheive this?

Solution

  • I would recommend first renaming your existing myfile.csv to something else (like myfile_[DateOfLastRun].csv). Then start fresh with a new myfile.csv. That way if something goes wrong outside this process and you need whatever was in myfile.csv the day/week/month before, you have it.

    You could use BCP for this in a BAT file:

    set vardate=%DATE:~4,10%
set varDateWithoutSlashes=%vardate:/=-%
bcp "SELECT someColumns FROM aTable" queryout myFile_%varDateWithoutSlashes%.csv -t, -c -T

    The example above creates your CSV with the date already in the name. You could also rename the existing file, then create your new myfile.csv without the date:

    set vardate=%DATE:~4,10%
set varDateWithoutSlashes=%vardate:/=-%
ren myFile.csv myFile_%varDateWithoutSlashes%.csv
bcp "SELECT someColumns FROM aTable" queryout myFile.csv -t, -c -T

    Be sure to build in cleanup of old files somewhere - that can even be done in the same batch process as this one.

    You can add DB name and server name to the bcp line - by default it connects to the local server and the user's default DB (See the BCP documentation link for even more options)

    bcp databaseName "SELECT someColumns FROM aTable" queryout myFile.csv -t, -c -T -S serverName