Zapisywanie zaznaczonego wyniku do pliku csv

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 niektórych powodów nie mamy tej opcji.

Próbowałem użyć sugerowanego proc w poniższym artykule, ale kiedy uruchamiam proc, SQL narzeka, że ​​nie można uruchomić sys.sp_OACreate i sys.sp_OADestroy, które są wywoływane w tym proc.

Czy wiesz, jak możemy włączyć te komponenty lub znasz lepszy sposób zapisu do pliku przy użyciu T-SQL?

Z góry dziękuję.

Użyj narzędzia BCP

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

W -cOkreśla argumentów c wyjściu haracter, w przeciwieństwie do natywnego formatu binarnego zapytania SQL za; ta domyślnie wartości rozdzielone zakładki, lecz -t ,zmienia pole t erminator do przecinkami. -Tokreśla uwierzytelnianie systemu Windows („ t zardzewiałe połączenie”), w przeciwnym razie użyj -U MyUserName -P MyPassword.

To nie eksportuje domyślnie nagłówków kolumn. Do nagłówków musisz użyć UNION ALL

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 PowerShell

Oto link do artykułu . Jeśli w końcu to wykorzystasz, możesz chcieć dołączyć -notypepo, Export-Csv $extractFileaby pozbyć się niepotrzebnej kolumny w pliku wyjściowym.

Dodając do poprzedniej odpowiedzi, która pomaga zautomatyzować akcję, jeśli potrzebujesz jej 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ściowy wyników na plik. Użyj Narzędzia -> Opcje -> Wyniki zapytania - Wyniki do pliku .

Innym sposobem, który można łatwo zautomatyzować i korzysta z SSIS, jest skorzystanie z funkcji eksportu danych Management Studio. Kliknij bazę danych prawym przyciskiem myszy -> Zadania -> Eksportuj dane. Istnieje kreator z wieloma opcjami. Powinieneś wybrać źródłową bazę danych, miejsce docelowe i inne opcje. W przypadku miejsca docelowego upewnij się, że jest to „plik płaski”, przeglądaj i wybierz typ .csv, wybierz dowolne potrzebne formatowanie, a na końcu otrzymasz pakiet SSIS, który można zapisać lokalnie i powtórzyć w razie potrzeby.

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ń:

1. Musisz mieć dostęp do dostawcy 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.
2. Plik .CSV musi już istnieć. Jeśli używasz nagłówków (HDR = TAK), upewnij się, że pierwszy wiersz pliku .CSV jest listą rozdzielaną wszystkich pól.

Niska technologia, ale ...

select
   col1 + ','
+  cast(col2 as varchar(10)) + ','
+  col3
from mytable;

Wygląda na to, że masz już akceptowalne rozwiązanie, ale jeśli masz skonfigurowaną pocztę db, możesz potencjalnie zrobić coś takiego:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='your.email@domain.com',
    @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 mimo to wysyłasz gdzieś plik, możesz zaoszczędzić kilka kroków.

Można to łatwo zrobić w 3 etapach:

1. Napisz zapytanie jako SELECT INTOzapytanie. 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 istnieć po uruchomieniu zapytania, ponieważ ten typ zapytania chce utworzyć tabelę w ramach wykonywania.

2. Następnie użyj SSIS, aby wyeksportować wyniki tej tabeli do .csv. Kreator eksportu SSIS pozwala wybrać ogranicznik itp. Jest to bardzo przydatne przy użyciu | ogranicznik w przypadkach, w których zestaw wyników zawiera przecinki.

   Kliknij prawym przyciskiem myszy DB Name > Tasks > Export Data

3. Po zakończeniu eksportu uruchom DROP TABLEpolecenie, aby wyczyścić tabelę dziennika.

   Drop Table dbo.LogTableName

Używam MS Access DoCMD do eksportowania CSV z SQL Server. Jest w stanie to zrobić za pomocą jednego polecenia. Baza danych dostępu służy wyłącznie do włączenia polecenia dostępu. Działa dobrze i jako dodatkowa korzyść (nie wiem dlaczego) tworzy 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"

Ta procedura wygeneruje plik CSV z danymi wyjściowymi zapytania przekazywanego jako parametr. Drugim parametrem jest nazwa pliku CSV, upewnij się, że przekazałeś nazwę pliku z prawidłową ścieżką do zapisu.

Oto skrypt do utworzenia 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