A SELECT lekérdezés eredményeit csv fájlba kell írnunk. Hogyan lehet megtenni a T-SQL használatával az SQL Server 2008 R2-ben? Tudom, hogy SSIS-ben is megtehető, de bizonyos okokból nincs ilyen lehetőségünk.
Megpróbáltam használni az alábbi cikkben a javasolt proc-ot, de amikor a proc-ot futtattam, SQL panaszkodik, amelyek nem futtathatják a sys.sp_OACreate és a sys.sp_OADestroy fájlokat, amelyeket ebben a procban hívnak meg.
Tudja, hogyan kapcsolhatjuk be ezeket az összetevőket, vagy tud-e még jobb módszert a fájlokba való írásra a T-SQL segítségével?
Előre is köszönöm.
Válasz
Használja a BCP segédprogram
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
A -c
argumentum megadja a c karakterkimenet, szemben az SQL natív bináris formátumával; ez alapértelmezés szerint tabulátorral elválasztott értékek, de -t ,
A t erminator mezőt vesszővé változtatja. -T
a Windows hitelesítést adja meg (“ t rozsdás kapcsolat “), különben használja a -U MyUserName -P MyPassword
.
Ez alapértelmezés szerint nem exportálja az oszlopfejléceket. UNION ALL-t kell használnia a fejlécekhez
VAGY
Használja az SQLCMD
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
VAGY
A Powershell használata
Itt található egy link egy cikkhez . Ha végül ezt használja, érdemes a -notype
fájlt a Export-Csv $extractFile
után felvenni, hogy megszabaduljon a kimeneti fájl felesleges oszlopától.
Megjegyzések
- +1 A BCP segédprogramhoz – nagyon egyszerűen használható, ez a leggyorsabb eszköz, amit valaha is használtam exportáláshoz / importáláshoz információ és sok lehetőség van. Azt tanácsolom, hogy adja hozzá a ” > FileName.log ” szöveget a utasítás – ez létrehoz egy naplófájlt.
- Minden olyan válasz, amely GUI programot tartalmaz, egyszerűen kívül esik. DBA szempontjából ez a legjobb megoldás!
Válasz
Hozzáadás az előző válaszhoz, amely segít a művelet automatizálásában, ha csak időnként van rá szüksége, megteheti a Management Studio-ban, egyszerűen kattintson jobb gombbal a fejlécre – Eredmények mentése másként -> válassza a lehetőséget. csv fájl .
Vagy ha ezt meg akarja tenni minden egyes futtatott kijelentésnél, megváltoztathatja az eredmények kimeneti irányát fájlra. Használja az eszközöket -> Opciók -> Lekérdezés eredménye – Eredmények a fájlba .
Egy másik mód, amely könnyen automatizálható, és az SSIS-t használja, a Management Studio Adatok exportálása funkciójának használatával. Kattintson jobb gombbal az adatbázisra -> Feladatok -> Adatok exportálása. Van egy varázsló, amely rengeteg opcióval rendelkezik. Válassza ki a forrásadatbázist, a rendeltetési helyet és az egyéb opciókat. A célállomáshoz győződjön meg róla, hogy “lapos fájl”, böngésszen és válassza a .csv típust , válassza ki a kívánt formázást, és a végén beszerez egy SSIS csomagot, amelyet helyileg el lehet menteni és szükség esetén meg lehet ismételni.
Válasz
A T-SQL használata
INSERT INTO OPENROWSET("Microsoft.ACE.OLEDB.12.0","Text;Database=D:\;HDR=YES;FMT=Delimited","SELECT * FROM [FileName.csv]") SELECT Field1, Field2, Field3 FROM DatabaseName
De van néhány figyelmeztetés:
- elérhető Microsoft.ACE.OLEDB.12.0 szolgáltató. A Jet 4.0 szolgáltató is működni fog, de ősi, ezért inkább ezt használtam.
- A .CSV fájlnak már léteznie kell. Ha fejléceket használ (HDR = YES) , győződjön meg arról, hogy a .CSV fájl első sora az összes mező elválasztott listája.
Megjegyzések
- A legjobb válasz így messze!
- nagyszerű munka, beleértve a figyelmeztetéseket
- Még egy figyelmeztetés: szükség lehet engedélyekre, különben a következőket fogja látni: Msg 15281, 16. szint, 1. állapot, 2. sor Az SQL Server letiltotta a hozzáférést a következőhöz: NYILATKOZAT ‘ OpenRowset / OpenDatasource ‘ a ‘ ad hoc elosztott lekérdezések ‘ mert ez az összetevő ki van kapcsolva a szerver biztonsági konfigurációjának részeként. A rendszergazda engedélyezheti az ‘ Ad Hoc terjesztett lekérdezések ‘ használatát az sp_configure használatával. Az ‘ ad hoc terjesztett lekérdezések ‘ engedélyezésével kapcsolatos további információk: ‘ Ad Hoc Terjesztett lekérdezések ‘ az SQL Server Books Online-ban.
Válasz
Alacsony technológiájú, de …
select col1 + "," + cast(col2 as varchar(10)) + "," + col3 from mytable;
Megjegyzések
- A MySQL kezeli a
col1 + ',' + cast(col2 as varchar(10)) + ',' + col3
oszlopnévként, és az egész helyre NULL-t nyomtat.
Válasz
Úgy tűnik, hogy már van elfogadható megoldása, de ha db levelezõ van beállítva, akkor potenciálisan tehet ilyesmit:
EXEC msdb.dbo.sp_send_dbmail @recipients="[email protected]", @subject="CSV Extract", @body="See attachment", @query ="SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable", @attach_query_result_as_file = 1, @query_attachment_filename = "CSV_Extract.txt", @query_result_separator = ",", @query_result_header = 1
Ha valahogy mégis e-mailben küldi el a fájlt, az megtakaríthat néhány lépést.
Megjegyzések
- Csak azt, amit kerestem, nagyra köszönöm!
- Bár hozzá kell adnia a @profile_name paramétert, ha nincs alapértelmezett privát profilja a felhasználó számára, sem az adatbázis nyilvános profilja.
Válasz
Ez könnyen elvégezhető 3 lépésben folyamat:
-
Írja be a lekérdezést
SELECT INTO
lekérdezésként. Ez lényegében exportálja a lekérdezés eredményeit egy táblába.Select Field1 ,Field2 ,Field3 INTO dbo.LogTableName --Table where the query Results get logged into. From Table
Megjegyzés: ez a táblázat nem akkor létezik, amikor a lekérdezés fut, mivel ez a típusú lekérdezés a táblát szeretné létrehozni a végrehajtás részeként.
-
Ezután használja az SSIS-t az exportáláshoz a táblázat eredményei a következőre:
.csv
. Az SSIS Export varázsló segítségével kiválaszthatja az elválasztót stb. Ez nagyon hasznos a | használatához határoló olyan esetekben, amikor az eredményhalmaz vesszőt tartalmaz.Kattintson a jobb gombbal a
DB Name > Tasks > Export Data
Miután az Exportálás befejeződött, hajtsa végre a DROP TABLE
parancs a naplótábla tisztításához.
Drop Table dbo.LogTableName
Megjegyzések
- Szia Don. Hozzáadtam néhány formázást a válaszához. Kattintson a ‘ szerkesztésre ‘ a bal alsó sarokban, hogy lássa, mit ‘ megváltozott, és kattintson a ” szerkesztett ” xx percre ” bitre a teljes előzmény megtekintéséhez ha vissza akar gördülni. Habár működőképes, a válasza nem ‘ nem sokat tesz hozzá a már megírt ‘ -hez, és aligha fog nagy figyelmet felkelteni.
Válasz
Ez az eljárás létrehoz egy CSV-fájlt, amelynek paramétereként átadja a lekérdezés kimenetét. A 2. paraméter a CSV fájlnév. Győződjön meg arról, hogy a fájlnevet érvényes írható elérési úttal adja meg.
Itt található az eljárás létrehozásának szkriptje:
CREATE procedure [dbo].[usp_create_csv_file_from_query] @sql Nvarchar(MAX), @csvfile Nvarchar(200) as BEGIN if IsNull(@sql,"") = "" or IsNull(@csvfile,"") = "" begin RAISERROR ("Invalid SQL/CsvFile values passed!; Aborting...", 0, 1) WITH NOWAIT return end DROP TABLE if exists global_temp_CSVtable; declare @columnList varchar(4000), @columnList1 varchar(4000), @sqlcmd varchar(4000), @dos_cmd nvarchar(4000) set @sqlcmd = replace(@sql, "from", "into global_temp_CSVtable from") EXECUTE (@sqlcmd); declare @cols table (i int identity, colname varchar(100)) insert into @cols select column_name from information_schema.COLUMNS where TABLE_NAME = "global_temp_CSVtable" declare @i int, @maxi int select @i = 1, @maxi = MAX(i) from @cols while(@i <= @maxi) begin select @sql = "alter table global_temp_CSVtable alter column [" + colname + "] VARCHAR(max) NULL" from @cols where i = @i exec sp_executesql @sql select @i = @i + 1 end SELECT @columnList = COALESCE(@columnList + """, """, "") + column_name ,@columnList1 = COALESCE(@columnList1 + ", ", "") + column_name from information_schema.columns where table_name = "global_temp_CSVtable" ORDER BY ORDINAL_POSITION; SELECT @columnList = """" + @columnList + """"; SELECT @dos_cmd="BCP "SELECT " + @columnList + " UNION ALL " + "SELECT * from " + db_name() + "..global_temp_CSVtable" " + "QUERYOUT " + @csvfile + " -c -t, -T" exec master.dbo.xp_cmdshell @dos_cmd, No_output DROP TABLE if exists global_temp_CSVtable; end
Megjegyzések
- csak a kódra adott válaszok ‘ itt nem tekinthetők elfogadhatónak – tudna néhány megjegyzést fűzni a válaszához, és miért működik? (a kódos megjegyzések valószínűleg itt is segítenek)
- Igen, és adhatna példát arra is, hogy miként használja sikeresen?
Válasz
Az MS Access DoCMD-t használom a CSV-fájlok exportálására az SQL Server szerverről. Egyetlen paranccsal képes megtenni. A hozzáférési adatbázist csak a hozzáférési parancs engedélyezésére használják. Jól teljesít, és további előnyként (nem tudni miért) létrehoz egy schema.ini fájlt az exportált táblához.
Adam
CMD="SELECT * INTO [Text;HDR=Yes;DATABASE=C:\].[Results.txt] FROM [ODBC;DSN=SQL2017;Description=SQL2017;UID=.;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=LAPTOP-XYZ;DATABASE=TempDB_DataDump_1].Results " Set appAccess = CreateObject("Access.Application") appAccess.OpenCurrentDatabase "anyAccessdatabase.mdb" " appAccess.DoCmd.SetWarnings 0 appAccess.DoCmd.runsql "CMD"