Wie man mit dem SQL-Server Text-Dateien einlesen kann, wusste ich schon länger. Dazu musste man früher OpenRowSet mit einem ODBC-Text-Treiber verwenden:
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR='E:\data;Extensions=txt;',
'SELECT * FROM [test.txt]') AS t;
Aber das war tricky und der Output war immer pro Zeile ein Datensatz. Freilich konnte man auch die Daten mittels "BULK INSERT" in eine Tabelle laden, aber auch das war umständlich. Seit SQL-Server-2005 gibt es dazu eine gute und dokumentierte Lösung:
SELECT x
FROM OPENROWSET(BULK 'E:\data\test.txt', SINGLE_CLOB) AS t(x);
Das funktioniert auch mit Bildern oder beliebigen anderen Dateien, dann ist es aber ein "SINGLE_BLOB". Aber das Schreiben ist so immer noch nicht möglich. Freilich kann man auch hier wieder mit OpenRowSet arbeiten. Der Text-Treiber kann aber keinen INSERT, daher muss man dann schon den Treiber für Access nehmen und so tun als wäre es eine csv-Datei. Mit dem gelang es aber nicht eine neue Datei anzulegen. Man musste also zuerst eine leere Datei via xp_cmsshell anlegen und konnte sie dann füllen. Das geht aber nur mit 32-Bit-SQL-Servern, weil es gibt keine 64-Bit-Treiber für die Jet-Engine gibt… 🙁
Bei Simple-Talk fand ich eine praktikable Lösung dazu: Man verwendet "bcp" via "xp_cmdshell", um die Daten in eine Text-Datei zu schreiben. Klingt schwierig, ist es aber nicht. Wie für alle obigen Lösungen benötigt man auch hier Admin-Rechte. Die Lösung taugt also nicht für den laufenden Betrieb, aber eignet sich prima für eigene administrative Aufgaben.
DECLARE @Cmd nvarchar(2000);
SET @Cmd = N'bcp "select t from [TestTable]" queryout "F:\temp\outfile.txt" -T -S'
+ @@servername + N' -w'; – "-w" für Unicode-Datei "-c" für ANSI
EXEC xp_cmdshell @Cmd;
Natürlich muss man die Verwendung von xp_cmdshell erst mal erlauben.
Hier steht der Trick im Detail beschrieben und etliches mehr: "The TSQL of Text Files"