創建每月作業以從 SQL Server 2014 導出到 Excel

這裡有很多問題詢問“如何從 SQL Server 導出到 Excel”，但是每個答案以及我所知道的東西都給我帶來了問題（這裡是 64 位系統）。

我們有一個開發人員每月執行 6 次查詢（從 2010 年、2011 年等更改年份）。然後，他將結果複製並粘貼到 Excel 電子表格中。

我在將這些數據從 SQL Server 2014 導出到 Excel 時遇到問題。

這是查詢：

SELECT 
R.numero,
R.ano,
R.data 'data abertura',
R.datahora_conclusao  'data conclusão',
R.datahora_emissao 'data emissão',
S.[status],
(ins.INSTITUTO +
           ' - ' + dir.DIRETORIA + 
           (CASE WHEN NOT nuc.NUCLEO IS NULL THEN  ' - ' + nuc.NUCLEO  ELSE '' END) +
           (CASE WHEN NOT equ.EQUIPE IS NULL THEN ' - ' + equ.EQUIPE ELSE '' END)) AS UNIDADE,
N.codigo 'codigo natureza exame',
Case E.cod_estadopreservacao when 1 then 'Preservado' when 2 then 'Não Preservado' else 'Laborátorio' end 'Local Exame',

u.nome 'Perito Designado'
FROM tbReps R 
LEFT Join tbExames E on E.cod_rep = R.cod_rep
LEFT JOIN tbUsuarios U ON U.cod_usuario = R.cod_perito_primeiro
LEFT JOIN tbUnidades               AS uni WITH(NOLOCK) ON R.cod_unidade = uni.cod_unidade
LEFT JOIN lstUnidadesInstitutos AS ins WITH(NOLOCK) ON uni.cod_instituto = ins.cod_instituto
LEFT JOIN lstUnidadesDiretorias AS dir WITH(NOLOCK) ON uni.cod_instituto = dir.cod_instituto AND uni.cod_diretoria = dir.cod_diretoria
LEFT JOIN lstUnidadesNucleos AS nuc WITH(NOLOCK) ON uni.cod_diretoria = nuc.cod_diretoria AND uni.cod_nucleo = nuc.cod_nucleo 
LEFT JOIN lstUnidadesEquipes AS equ WITH(NOLOCK) ON uni.cod_nucleo = equ.cod_nucleo and uni.cod_equipe = equ.cod_equipe
LEFT JOIN lstNaturezasExame N ON R.cod_naturezaexame = N.cod_naturezaexame
LEFT JOIN lstStatus S ON S.cod_status = R.cod_status
WHERE  year(r.data) = 2016 and r.data <= '2016-04-30 23:59:59' 
order by data

我正在想辦法將其直接導入 Excel 文件，按年份分隔。我認為創建 6 個表（2010…2011…2012）並使用該Task > Export過程，然後刪除它們是個好主意。但我認為有更好（更正確）的方法來做到這一點。

我應該使用哪個 OLE.DB 提供程序？有腳本可以做到這一點嗎？一些我不知道的程序？

我with (nolock)到處使用僅用於測試目的。

PowerShell是要走的路：

# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by     : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
                   # Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !

# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "servername\INSTANCE1"  #YourServer Name e.g. server1\instance1
$database = "your_db_name"    #your database name
$query = "select some columns from table inner join other table on table.id = othertable.id"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
# change here to the path that you want
$extractFile = @"
C:\SQLtoExcel.csv  
"@

# Use windows authentication !!
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
# -NoTypeInformation will remove the first #TYPE System.Data.DataRow
$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

也可以看看：

Kevin Feasel通過 Powershell 實現的多標籤 Excel

引用自：https://dba.stackexchange.com/questions/140618