Precisamos gravar os resultados da consulta SELECT em um arquivo csv. Como isso pode ser feito usando o T-SQL no SQL Server 2008 r2? Eu sei que isso pode ser feito no SSIS, mas por alguns motivos, não temos essa opção.
Tentei usar o proc sugerido no artigo abaixo, mas quando executo o proc, SQL reclama que não é possível executar sys.sp_OACreate e sys.sp_OADestroy, que são chamados neste proc.
Você sabe como podemos ativar esses componentes ou conhece alguma maneira melhor de gravar em um arquivo usando T-SQL?
Agradecemos antecipadamente.
Resposta
Use Utilitário BCP
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
O argumento -c
especifica c saída de haracter, em oposição ao formato binário nativo do SQL; o padrão é valores separados por tabulação, mas -t ,
altera o campo t erminator para vírgulas. -T
especifica a autenticação do Windows (“ t conexão enferrujada “), caso contrário, use -U MyUserName -P MyPassword
.
Isso não exporta cabeçalhos de coluna por padrão. Você precisa usar UNION ALL para cabeçalhos
OU
Use SQLCMD
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
OU
Use Powershell
Aqui está um link para um artigo . Se você acabar usando isso, pode querer acrescentar -notype
depois de Export-Csv $extractFile
para se livrar da coluna desnecessária no arquivo de saída.
Comentários
- +1 Para o utilitário BCP – é muito fácil de usar, é a ferramenta mais rápida que já usei para exportar / importar informações e há muitas opções. Aconselho você a adicionar ” > FileName.log ” no final do declaração – isso criará um arquivo de log.
- Qualquer resposta envolvendo um programa GUI está simplesmente fora do escopo. Esta é a melhor solução do ponto de vista do DBA!
Resposta
Adicionando à resposta anterior, que ajuda a automatizar a ação, se você precisar apenas de vez em quando, pode fazê-lo no Management Studio, simplesmente clique com o botão direito do mouse no cabeçalho – Salvar resultados como -> escolha a. arquivo csv .
Ou, se quiser fazer isso para cada instrução select que executar, poderá alterar a direção de saída dos resultados para um arquivo. Use Ferramentas -> Opções -> Resultados da consulta – Resultados para o arquivo .
Outra maneira, que pode ser automatizado facilmente e usar o SSIS é usar o recurso Exportar Dados do Management Studio. Clique com o botão direito no banco de dados -> Tarefas -> Exportar dados. Existe um assistente com várias opções. Você deve escolher o banco de dados de origem, o destino e outras opções. Para o destino certifique-se de “s” Arquivo plano “, navegue e escolha o tipo .csv , escolha a formatação necessária e, no final, você obterá um pacote SSIS que pode ser salvo localmente e repetido quando necessário.
Resposta
Use 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
Mas, há algumas ressalvas:
- Você precisa ter o provedor Microsoft.ACE.OLEDB.12.0 disponível. O provedor Jet 4.0 também funcionará, mas é antigo, então usei este.
- O arquivo .CSV já deve existir. Se você estiver usando cabeçalhos (HDR = SIM) , certifique-se de que a primeira linha do arquivo .CSV seja uma lista delimitada de todos os campos.
Comentários
- Melhor resposta então agora!
- ótimo trabalho, incluindo advertências
- Mais uma advertência: você pode precisar de permissões, caso contrário, verá: Msg 15281, Nível 16, Estado 1, Linha 2 SQL Server bloqueou o acesso a DECLARAÇÃO ‘ OpenRowset / OpenDatasource ‘ do componente ‘ Consultas distribuídas ad hoc ‘ porque este componente está desligado como parte da configuração de segurança para este servidor. Um administrador de sistema pode habilitar o uso de ‘ Consultas distribuídas ad hoc ‘ usando sp_configure. Para obter mais informações sobre como ativar ‘ Consultas distribuídas ad hoc ‘, pesquise por ‘ Ad Hoc Consultas distribuídas ‘ nos manuais online do SQL Server.
Resposta
Baixa tecnologia, mas …
select col1 + "," + cast(col2 as varchar(10)) + "," + col3 from mytable;
Comentários
- Mimos do MySQL
col1 + ',' + cast(col2 as varchar(10)) + ',' + col3
como um nome de coluna e imprime NULL em todo o lugar.
Resposta
Parece que você já tem uma solução aceitável, mas se você configurou o db mail poderia potencialmente fazer algo assim:
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
Se você estiver enviando o arquivo para algum lugar por e-mail de qualquer maneira, isso pode economizar algumas etapas.
Comentários
- Exatamente o que eu estava procurando, parabéns para você!
- Embora você precise adicionar o parâmetro @profile_name se você não tiver um perfil privado padrão para o seu usuário nem perfil público para o banco de dados.
Resposta
Isso pode ser feito facilmente em 3 etapas processo:
-
Escreva sua consulta como uma consulta
SELECT INTO
. Isso basicamente exportará os resultados da consulta para uma tabela.Select Field1 ,Field2 ,Field3 INTO dbo.LogTableName --Table where the query Results get logged into. From Table
Observe, esta tabela não pode existem quando a consulta é executada, pois este tipo de consulta deseja criar a tabela como parte da execução.
-
Em seguida, use o SSIS para exportar os resultados dessa tabela para
.csv
. O assistente de exportação do SSIS permite que você escolha seu delimitador, etc. Isso é realmente útil para usar um | delimitador em instâncias onde o conjunto de resultados contém vírgulas.Clique com o botão direito em
DB Name > Tasks > Export Data
-
Assim que a exportação for concluída, execute o
DROP TABLE
comando para limpar sua tabela de log.Drop Table dbo.LogTableName
Comentários
- Olá, Don. Eu adicionei alguma formatação à sua resposta. Você pode clicar em ‘ editar ‘ no canto inferior esquerdo para ver o que ‘ s alterado e clique no bit ” editado ” xx minutos ” para ver um histórico completo se você quiser reverter. Embora viável, sua resposta não ‘ acrescenta muito sobre o que ‘ já foi escrito e provavelmente não atrairá muita atenção.
Resposta
Este procedimento irá gerar um arquivo CSV com a saída da consulta que você passar como parâmetro. O segundo parâmetro é o nome do arquivo CSV, certifique-se de passar o nome do arquivo com um caminho de escrita válido.
Aqui está o script para criar o procedimento:
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
Comentários
- respostas apenas de código não são ‘ vistas como aceitáveis aqui – você poderia adicionar algum comentário sobre sua resposta e por que ela funciona? (comentários de código provavelmente ajudariam aqui também)
- Sim, e você também poderia adicionar um exemplo de como pode usá-lo com sucesso?
Resposta
Eu uso o MS Access DoCMD para exportar CSV “s do SQL Server. Ele é capaz de fazer isso com um único comando. O banco de dados de acesso é usado apenas para habilitar o comando de acesso. Ele tem um bom desempenho e, como um benefício adicional (não sei por que), cria um schema.ini para a tabela exportada.
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"