Musimy zapisać wyniki zapytania SELECT do pliku csv. Jak można to zrobić za pomocą T-SQL w SQL Server 2008 R2? Wiem, że można to zrobić w SSIS, ale z pewnych powodów nie mamy tej opcji.
Próbowałem użyć sugerowanej procedury w artykule poniżej, ale kiedy uruchomię proc, SQL narzeka, że nie można „t uruchomić sys.sp_OACreate i sys.sp_OADestroy, które są wywoływane w tym procencie.
Czy wiesz, jak możemy włączyć te komponenty lub znasz lepszy sposób zapisywania do pliku przy użyciu T-SQL?
Z góry dziękuję.
Odpowiedź
Użyj Narzędzie BCP
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
Argument -c
określa c dane wyjściowe, w przeciwieństwie do natywnego formatu binarnego SQL; domyślnie są to wartości rozdzielane tabulatorami, ale -t ,
zmienia pole t erminator na przecinki. -T
określa uwierzytelnianie systemu Windows („ t zardzewiałe połączenie „), w przeciwnym razie użyj -U MyUserName -P MyPassword
.
Domyślnie to nie eksportuje nagłówków kolumn. Musisz użyć UNION ALL dla nagłówków
LUB
Użyj SQLCMD
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
LUB
Użyj programu PowerShell
Oto link do artykułu . Jeśli skończysz z tym, możesz dodać -notype
po Export-Csv $extractFile
, aby pozbyć się niepotrzebnej kolumny z pliku wyjściowego.
Komentarze
- +1 Dla narzędzia BCP – jest bardzo łatwe w użyciu, jest to najszybsze narzędzie, jakiego kiedykolwiek używałem do eksportu / importu informacji i jest wiele opcji. Radzę dodać ” > FileName.log ” na końcu instrukcja – spowoduje to utworzenie pliku dziennika.
- Każda odpowiedź dotycząca programu GUI jest po prostu poza zakresem. To najlepsze rozwiązanie z punktu widzenia DBA!
Odpowiedź
Dodanie do poprzedniej odpowiedzi, która pomaga zautomatyzować działanie, jeśli potrzebujesz tego tylko od czasu do czasu, możesz to zrobić w Management Studio, po prostu kliknij prawym przyciskiem myszy nagłówek – Zapisz wyniki jako -> wybierz plik. csv .
Lub jeśli chcesz to zrobić dla każdej uruchamianej instrukcji select, możesz zmienić kierunek wyjścia wyników na plik. Użyj Narzędzia -> Opcje -> Wyniki zapytania – Wyniki do pliku .
Innym sposobem, który może być łatwo zautomatyzowane i korzysta z SSIS za pomocą funkcji eksportu danych Management Studio. Kliknij prawym przyciskiem myszy bazę danych -> Zadania -> Eksportuj dane. Jest tam kreator z wieloma opcjami. Powinieneś wybrać źródłową bazę danych, miejsce docelowe i inne opcje. Dla miejsca docelowego upewnij się, że jest to „Plik płaski”, przeglądaj i wybierz typ .csv , wybierz dowolne potrzebne formatowanie, a na końcu uzyskasz pakiet SSIS, który można zapisać lokalnie i powtórzyć w razie potrzeby.
Odpowiedź
Użyj 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
Ale jest kilka zastrzeżeń:
- Musisz mieć dostępny dostawca Microsoft.ACE.OLEDB.12.0. Dostawca Jet 4.0 też będzie działał, ale jest stary, więc zamiast tego użyłem tego.
- Plik .CSV będzie musiał już istnieć. Jeśli używasz nagłówków (HDR = YES) , upewnij się, że pierwsza linia pliku .CSV jest rozdzielaną listą wszystkich pól.
Komentarze
- Najlepsza odpowiedź, więc daleko!
- świetna robota z zastrzeżeniami
- Jeszcze jedno zastrzeżenie: możesz potrzebować uprawnień, w przeciwnym razie zobaczysz: Msg 15281, poziom 16, stan 1, wiersz 2 SQL Server zablokował dostęp do STATEMENT ' OpenRowset / OpenDatasource ' komponentu ' Zapytania rozproszone ad hoc , ponieważ ten składnik jest wyłączony w ramach konfiguracji zabezpieczeń tego serwera. Administrator systemu może włączyć używanie ' kwerend rozproszonych ad hoc ' za pomocą sp_configure. Aby uzyskać więcej informacji na temat włączania ' zapytań rozproszonych ad hoc ', wyszukaj ' ad hoc Rozproszone zapytania ' w SQL Server Books Online.
Odpowiedź
Niska technologia, ale …
select col1 + "," + cast(col2 as varchar(10)) + "," + col3 from mytable;
Komentarze
- MySQL traktuje
col1 + ',' + cast(col2 as varchar(10)) + ',' + col3
jako nazwę kolumny i wyświetla NULL w każdym miejscu.
Odpowiedź
Wygląda na to, że masz już akceptowalne rozwiązanie, ale jeśli masz skonfigurowaną bazę danych db mail może potencjalnie zrobić coś takiego:
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
Jeśli i tak wysyłasz plik e-mailem, może to zaoszczędzić kilka kroków.
Komentarze
- Właśnie to, czego szukałem, brawa dla Ciebie!
- Chociaż będziesz musiał dodać parametr @profile_name, jeśli nie masz domyślnego profilu prywatnego dla Twojego użytkownika ani profilu publicznego bazy danych.
Odpowiedź
Można to łatwo zrobić w 3 krokach proces:
-
Napisz zapytanie jako zapytanie
SELECT INTO
. To zasadniczo wyeksportuje wyniki zapytania do tabeli.Select Field1 ,Field2 ,Field3 INTO dbo.LogTableName --Table where the query Results get logged into. From Table
Uwaga, ta tabela nie może istnieją, gdy zapytanie jest uruchamiane, ponieważ ten typ zapytania chce utworzyć tabelę jako część wykonania.
-
Następnie użyj SSIS do eksportu wyniki tej tabeli do
.csv
. Kreator eksportu SSIS umożliwia wybranie separatora itp. Jest to bardzo przydatne w przypadku używania znaku | separator w przypadkach, gdy zestaw wyników zawiera przecinki.Kliknij prawym przyciskiem myszy
DB Name > Tasks > Export Data
-
Po zakończeniu eksportu wykonaj
DROP TABLE
, aby wyczyścić tabelę dziennika.Drop Table dbo.LogTableName
Komentarze
- Cześć, Don. Dodałem trochę formatowania do Twojej odpowiedzi. Możesz kliknąć ' edytuj ' w lewym dolnym rogu, aby zobaczyć, co ' s zmieniony i kliknij ” edytowany ” xx minut „, aby wyświetlić pełną historię jeśli chcesz się cofnąć. Chociaż jest to wykonalne, Twoja odpowiedź nie ' nie dodaje zbyt wiele do tego, co ' zostało już napisane i jest mało prawdopodobne, aby przyciągnęła uwagę.
Odpowiedź
Ta procedura wygeneruje plik CSV z wynikiem zapytania, które przekazujesz jako parametr. Drugi parametr to nazwa pliku CSV, upewnij się, że przekazujesz nazwę pliku z prawidłową ścieżką do zapisu.
Oto skrypt służący do tworzenia 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
Komentarze
- tylko kod odpowiedzi nie są ' nie są tutaj akceptowalne – czy możesz dodać komentarz do swojej odpowiedzi i dlaczego to działa? (komentarze do kodu prawdopodobnie też by tu pomogły)
- Tak, czy możesz też dodać przykład, jak z powodzeniem go używasz?
Odpowiedź
Używam MS Access DoCMD do eksportowania CSV z SQL Server. Jest to w stanie zrobić za pomocą jednego polecenia. Baza danych Access jest używana tylko do włączenia polecenia dostępu. Działa dobrze, a jako dodatkowa korzyść (nie wiem dlaczego) tworzy plik schema.ini dla eksportowanej tabeli.
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"