Meidän on kirjoitettava SELECT-kyselyn tulokset csv-tiedostoon. Kuinka se voidaan tehdä käyttämällä T-SQL: ää SQL Server 2008 R2: ssa? Tiedän, että se voidaan tehdä SSIS: ssä, mutta jostain syystä meillä ei ole tätä vaihtoehtoa.
Yritin käyttää ehdotettua procia alla olevassa artikkelissa, mutta kun suoritan procia, SQL valittaa, että ”ei voi suorittaa sys.sp_OACreate ja sys.sp_OADestroy, joita kutsutaan tässä procissa.
Tiedätkö kuinka voimme ottaa nämä komponentit käyttöön tai tiedätkö parempia tapoja kirjoittaa tiedostoon T-SQL: n avulla?
Kiitos etukäteen.
Vastaa
Käytä BCP-apuohjelma
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
-c
-argumentti määrittää c merkkilähtö, toisin kuin SQL: n natiivi binaarimuodossa; tämä on oletuksena sarkaimilla erotetut arvot, mutta -t ,
muuttaa kentän t erminator pilkuiksi. -T
määrittää Windows-todennuksen (” t ruostunut yhteys ”), muuten käytä -U MyUserName -P MyPassword
.
Tämä ei vie sarakeotsikoita oletusarvoisesti. Sinun on käytettävä otsikoissa UNION ALL
TAI
Käytä SQLCMD
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
TAI
Käytä Powershell
Tässä on -linkki artikkeliin . Jos päätät käyttää tätä, kannattaa ehkä liittää -notype
Export-Csv $extractFile
: n jälkeen päästäksesi eroon tarpeettomasta sarakkeesta lähtötiedostossa.
kommentit
- +1 BCP-apuohjelmalle – se on erittäin helppo käyttää, se on nopein työkalu, jota olen koskaan käyttänyt viennissä / tuonnissa tietoa ja vaihtoehtoja on paljon. Kehotan sinua lisäämään ” > FileName.log ” lause – tämä luo lokitiedoston.
- Kaikki GUI-ohjelmaa koskevat vastaukset ovat yksinkertaisesti soveltamisalan ulkopuolella. Tämä on paras ratkaisu DBA: n näkökulmasta!
Vastaa
Lisätään edelliseen vastaukseen, joka auttaa sinua automatisoimaan toiminnon, jos tarvitset sitä vain ajoittain, voit tehdä sen Management Studiossa, napsauta hiiren kakkospainikkeella otsikkoa – Tallenna tulokset nimellä -> valitse a. csv-tiedosto .
Tai jos haluat tehdä sen jokaiselle valitsemallesi lauseelle, voit muuttaa tulosten ulostulosuunnan tiedostoksi. Käytä Työkalut -> Asetukset -> Kyselytulokset – Tulokset tiedostoon .
Toinen tapa, joka voi voidaan automatisoida helposti ja käyttää SSIS: ää, käyttämällä Management Studion Export Data -ominaisuutta. Napsauta hiiren kakkospainikkeella tietokantaa -> Tehtävät -> Vie tiedot. Siellä on ohjattu toiminto, jossa on paljon vaihtoehtoja. Valitse lähdetietokanta, kohde ja muut vaihtoehdot. Varmista, että kohde on ”Flat tiedosto”, selaa ja valitse .csv-tyyppi , valitse haluamasi muotoilu ja lopuksi hankit SSIS-paketin, joka voidaan tallentaa paikallisesti ja tarvittaessa toistaa.
Vastaa
Käytä 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
Mutta siinä on pari varoitusta:
- Sinulla on oltava käytettävissä oleva Microsoft.ACE.OLEDB.12.0-palveluntarjoaja. Jet 4.0 -palveluntarjoaja toimii myös, mutta se on ikivanha, joten käytin tätä.
- .CSV-tiedoston on oltava jo olemassa. Jos käytät otsikoita (HDR = YES) , varmista, että .CSV-tiedoston ensimmäinen rivi on eroteltu luettelo kaikista kentistä.
Kommentit
- Paras vastaus kaukana!
- loistava työ, mukaan lukien varoitukset
- Vielä yksi varoitus: saatat tarvita käyttöoikeuksia, muuten näet: Msg 15281, taso 16, tila 1, rivi 2 SQL Server estää pääsyn STATEMENT ’ OpenRowset / OpenDatasource ’ komponentin ’ Ad Hoc jaetut kyselyt ’, koska tämä komponentti on kytketty pois päältä tämän palvelimen suojausmäärityksissä. Järjestelmänvalvoja voi sallia ’ Ad Hoc -jaettujen kyselyjen ’ käytön sp_configure-toiminnolla. Lisätietoja ’ Ad Hoc -jaettujen kyselyjen ’ ottamisesta käyttöön hakemalla ’ Ad Hoc Jaetut kyselyt ’ SQL Server Books Onlinessa.
Vastaa
Matala tekniikka, mutta …
select col1 + "," + cast(col2 as varchar(10)) + "," + col3 from mytable;
Kommentit
- MySQL kohtelee
col1 + ',' + cast(col2 as varchar(10)) + ',' + col3
sarakkeen nimenä ja tulostaa NULL kaikkialle.
vastaus
Näyttää siltä, että sinulla on jo hyväksyttävä ratkaisu, mutta jos sinulla on db-postia, olet asettanut sinut voisi mahdollisesti tehdä jotain tällaista:
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
Jos lähetät tiedostoa joka tapauksessa, se saattaa säästää sinulle muutaman askeleen.
Kommentit
- Juuri mitä etsin, kiitos!
- Sinun on kuitenkin lisättävä @profile_name -parametri, jos sinulla ei ole oletusarvoista yksityistä profiilia käyttäjälle eikä julkinen profiili tietokannalle.
Vastaa
Tämä voidaan tehdä helposti 3 vaiheessa prosessi:
-
Kirjoita kyselysi
SELECT INTO
-kyselyksi. Tämä vie kyselytulokset lähinnä taulukkoon.Select Field1 ,Field2 ,Field3 INTO dbo.LogTableName --Table where the query Results get logged into. From Table
Huomaa, että tämä taulukko ei voi on olemassa, kun kysely suoritetaan, koska tämäntyyppinen kysely haluaa luoda taulukon osana suoritusta.
-
Käytä sitten SSIS-vientiä taulukon tulokset
.csv
. Ohjatun SSIS-vientitoiminnon avulla voit valita erottimen jne. Tämä on todella kätevää | erotin tapauksissa, joissa tulosjoukko sisältää pilkkuja.Napsauta hiiren kakkospainikkeella kohtaa
DB Name > Tasks > Export Data
-
Kun vienti on valmis, suorita
DROP TABLE
-komento siivota lokitaulukko.Drop Table dbo.LogTableName
Kommentit
- Hei Don. Lisäsin vastaukseen muotoilun. Voit napsauttaa ’ muokata ’ vasemmassa alakulmassa nähdäksesi mitä ’ s muutettu ja napsauta ” muokattua ” xx minuuttia ” nähdäksesi koko historian jos haluat palata takaisin. Vaikka vastauksesi on toimiva, vastauksesi ’ ei lisää paljoa sen yli, mitä ’ on jo kirjoitettu, eikä todennäköisesti herätä paljon huomiota.
Vastaus
Tämä toimenpide luo CSV-tiedoston, jonka parametrina välität kyselyn lähdön. Toinen parametri on CSV-tiedostonimesi. Varmista, että välität tiedostonimen kelvollisella kirjoitettavalla polulla.
Tässä on komentosarja menettelyn luomiseksi:
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
Kommentit
- vain koodivastauksia ei ’ pidetä hyväksyttävänä tässä – voisitko lisätä kommentin vastaukseesi ja miksi se toimii? (Koodikommentit todennäköisesti auttaisivat tässäkin)
- Kyllä, ja voisitko lisätä myös esimerkin siitä, kuinka käytät sitä onnistuneesti?
Vastaa
Käytän MS Access DoCMD: tä vietäessäni CSV-tiedostoja SQL Server -palvelimelta. Se pystyy tekemään sen yhdellä komennolla. Käyttötietokantaa käytetään vain pääsykomennon käyttöönottoon. Se toimii hyvin, ja lisäetuna (ei tiedä miksi) se luo schema.ini viedylle taulukolle.
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"