Výsledky dotazu SELECT musíme zapsat do souboru CSV. Jak to lze provést pomocí T-SQL v SQL Server 2008 R2? Vím, že to lze provést v SSIS, ale z nějakého důvodu tuto možnost nemáme.
Snažil jsem se použít navrhovaný proc v následujícím článku, ale když spustím proc, SQL si stěžuje, že nemůže spustit sys.sp_OACreate a sys.sp_OADestroy, které jsou volány v tomto proc.
Víte, jak můžeme tyto komponenty zapnout, nebo znáte nějaký lepší způsob zápisu do souboru pomocí T-SQL?
Předem děkujeme.
Odpovědět
Použít Nástroj BCP
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
Argument -c
určuje c charakteristický výstup, na rozdíl od nativního binárního formátu SQL; výchozí hodnoty jsou hodnoty oddělené tabulátory, ale -t ,
změní pole t erminator na čárky. -T
specifikuje ověřování Windows („ t rezavé připojení „), jinak použijte -U MyUserName -P MyPassword
.
Toto ve výchozím nastavení neexportuje záhlaví sloupců. Pro záhlaví musíte použít UNION ALL
NEBO
Použijte SQLCMD
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
NEBO
Použít Powershell
Zde je odkaz na článek . Pokud toto nakonec použijete, můžete přidat -notype
za Export-Csv $extractFile
, abyste se zbavili zbytečného sloupce ve výstupním souboru.
Komentáře
- +1 Pro nástroj BCP – jeho použití je velmi snadné, je to nejrychlejší nástroj, jaký jsem kdy použil pro export / import informace a existuje spousta možností. Doporučím vám přidat “ > FileName.log “ na konec prohlášení – vytvoří se soubor protokolu.
- Jakákoli odpověď zahrnující program GUI je prostě mimo rozsah. Toto je nejlepší řešení z pohledu DBA!
Odpověď
Přidání k předchozí odpovědi, která pomáhá vám automatizovat akci, pokud ji potřebujete jen čas od času, můžete to udělat v Management Studio, jednoduše klikněte pravým tlačítkem na záhlaví – Uložit výsledky jako -> vyberte a. soubor CSV .
Nebo to chcete udělat pro každý spuštěný příkaz select, můžete změnit směr výstupu výsledků na soubor. Použijte Nástroje -> Možnosti -> Výsledky dotazu – Výsledky do souboru .
Jiný způsob, který může lze snadno automatizovat a využívá SSIS pomocí funkce Export Data pro Management Studio. Klikněte pravým tlačítkem na databázi -> Úkoly -> Exportovat data. K dispozici je průvodce se spoustou možností. Měli byste zvolit zdrojovou databázi, cíl a další možnosti. U cíle se ujistěte, že je to „Plochý soubor“, procházejte a vyberte typ .csv , vyberte libovolné formátování, které potřebujete, a na konci získáte balíček SSIS, který lze lokálně uložit a podle potřeby opakovat.
Odpovědět
Použijte T-SQL
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
Existuje však několik upozornění:
- Musíte mít poskytovatel Microsoft.ACE.OLEDB.12.0 k dispozici. Poskytovatel Jet 4.0 bude také fungovat, ale je to staré, takže jsem místo toho použil tento.
- Soubor .CSV již bude muset existovat. Pokud používáte záhlaví (HDR = YES) , ujistěte se, že první řádek souboru .CSV je oddělený seznam všech polí.
Komentáře
- Nejlepší odpověď, takže daleko!
- skvělá práce včetně upozornění
- ještě jedna upozornění: možná budete potřebovat oprávnění, jinak uvidíte: zpráva 15281, úroveň 16, stav 1, řádek 2 SQL Server zablokoval přístup k PROHLÁŠENÍ ‚ OpenRowset / OpenDatasource ‚ komponenty ‚ Ad Hoc distribuované dotazy ‚ protože tato součást je vypnutá jako součást konfigurace zabezpečení pro tento server. Správce systému může povolit použití ‚ Ad Hoc distribuovaných dotazů ‚ pomocí sp_configure. Další informace o povolení ‚ distribuovaných dotazů Ad Hoc ‚ vyhledejte ‚ Ad Hoc Distribuované dotazy ‚ v SQL Server Books Online.
Odpověď
Nízká technologie, ale …
select col1 + "," + cast(col2 as varchar(10)) + "," + col3 from mytable;
Komentáře
- MySQL zachází s
col1 + ',' + cast(col2 as varchar(10)) + ',' + col3
jako název sloupce a vytiskne NULL všude.
Odpověď
Vypadá to, že již máte přijatelné řešení, ale pokud máte nastaven db mail může potenciálně udělat něco takového:
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
Pokud soubor přesto někam posíláte e-mailem, může vám to ušetřit několik kroků.
Komentáře
- Přesně to, co jsem hledal, chci vám říkat!
- Ačkoli nemáte žádný výchozí soukromý profil, budete muset přidat parametr @profile_name. pro vašeho uživatele ani pro veřejný profil pro databázi.
Odpověď
To lze snadno provést ve 3 krocích process:
-
Napište dotaz jako
SELECT INTO
dotaz. Tím se v podstatě exportují výsledky dotazu do tabulky.Select Field1 ,Field2 ,Field3 INTO dbo.LogTableName --Table where the query Results get logged into. From Table
Upozorňujeme, že tato tabulka nemůže existují při spuštění dotazu, protože tento typ dotazu chce vytvořit tabulku jako součást provádění.
-
Potom použijte SSIS k exportu výsledky této tabulky na
.csv
. Průvodce exportem SSIS umožňuje vybrat oddělovač atd. To je opravdu užitečné pro použití | oddělovač v případech, kdy sada výsledků obsahuje čárky.Klikněte pravým tlačítkem na
DB Name > Tasks > Export Data
-
Po dokončení exportu proveďte
DROP TABLE
příkaz k vyčištění tabulky protokolu.Drop Table dbo.LogTableName
Komentáře
- Ahoj Done. K vaší odpovědi jsem přidal nějaké formátování. Kliknutím na ‚ upravit ‚ v levém dolním rohu zobrazíte, co ‚ s změněno a kliknutím na bit “ upraveno “ xx minut “ zobrazíte úplnou historii pokud se chcete vrátit zpět. I když je to proveditelné, vaše odpověď ‚ nepřidá příliš mnoho k tomu, co již ‚ bylo napsáno, a je nepravděpodobné, že by vzbudila větší pozornost.
Odpověď
Tento postup vygeneruje soubor CSV s výstupem dotazu, který předáte jako parametr. Druhým parametrem je název souboru CSV. Ujistěte se, že jste předali název souboru s platnou zapisovatelnou cestou.
Zde je skript k vytvoření procedury:
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
Komentáře
- Odpovědi na kód pouze nejsou ‚ zde považovány za přijatelné – můžete ke své odpovědi přidat nějaký komentář a proč to funguje? (komentáře k kódu by pravděpodobně pomohly i zde)
- Ano, a můžete také přidat příklad toho, jak jej úspěšně používáte?
Odpovědět
K exportu souborů CSV ze serveru SQL používám program MS Access DoCMD. Je to možné provést jediným příkazem. Přístupová databáze se používá pouze k povolení příkazu access. Funguje dobře a jako další výhoda (nevím proč) vytvoří pro exportovanou tabulku soubor schema.ini.
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"