問題描述

我對 Powershell 還很陌生，需要一些有關導出 SQL 服務器查詢輸出的指導.

I am fairly new to Powershell and need some guidance on exporting SQL server query output.

我們有一個非常大的表，我們要從中導出數據.大小很大，因此將根據我們將提供的日期范圍提取數據.因此，對于多個日期范圍，我們將導出多個 csv 文件.作為其中的一部分，我們將使用日期范圍在表之間運行一些查詢，并導出輸出.我曾嘗試使用 SQL 服務器的內置功能 BCP，但輸出不包含任何標題，格式也是一個問題.該表非常龐大，并且包含大約 500 多個列，因此使用 UNION 顯示標題不是使用 BCP 的選項.該查詢將由不太熟悉 SQL 的人使用，因此也不推薦使用 GUI.因此想檢查我們是否可以使用 powershell 做到這一點.下面是我在 SQL 中創建的查詢類型:

We are having a very large table from which we want to export data. The size is huge so data will be extracted based on date ranges we are going to give. So for multiple date ranges we will be exporting multiple csv files. As part of this we will be running some queries with joins between tables using the date ranges and the output will be exported. I have tried using inbuilt functionality BCP of SQL server but the output does not contain any headers and formatting is also an issue. The table is very huge and large number of columns that is around 500+ so using UNION for displaying headers is not an option using BCP. The query will be used by people not so familiar with SQL so GUI also not recommended. Hence wanted to check if we can do this using powershell. Below is the kind of the query i have created in SQL:

declare @date1 nchar(8)
declare @date2 nchar(8)
DECLARE @ExportFolderName NVARCHAR(90);
DECLARE @ExportFileName NVARCHAR(90);
Declare @db_name nvarchar(90)
--create temporary table
create table ##temp(
USER bigint,
USER_ID nvarchar(100)
);
--insert date ranges as required
Create table #dates(todate nchar(8), fromdate nchar(8))
insert into #dates values('20200501','20200601')
insert into #dates values('20200602','20200701')
insert into #dates values('20200702','20200801')
insert into #dates values('20200802','20200901')
select @db_name=db_name();
DECLARE database_cursor CURSOR FOR 
select todate,fromdate from #dates
OPEN database_cursor 
FETCH NEXT FROM database_cursor INTO @Date1,@date2
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @ExportFolderName = 'C:\test\';
SET @ExportFileName = @ExportFolderName + @db_name+'_logs_' + RTRIM(CONVERT(NVARCHAR(20), @date2,112)) + '.csv';
insert into ##temp
(USER, USER_ID)
(
**************
);
DECLARE @SqlStatement nvarchar(1000)
DECLARE @BcpStatement nvarchar(1000)
SET @SqlStatement = 'select * from dbo.logs E with (nolock) inner join ##temp U on E.USER=U.USER  and E.USER_ID COLLATE =U.USER_ID where E.DATE1 >= '''+@date1+''' and E.DATE1 < '''+@date2+''' order by event_id'
--print @sqlstatement
SET @BcpStatement = 'bcp "' + @SqlStatement + '" queryout "' + @ExportFileName + '" -c -S SERVER -d DAtabase -U Login -P PWD'
exec master..xp_cmdshell @BcpStatement
--print @bcpstatement
  FETCH NEXT FROM database_cursor INTO @Date1,@date2 
END 
CLOSE database_cursor 
DEALLOCATE database_cursor 
drop table ##temp
drop table #dates

我相信我們有類似下面的東西可以從 .sql 文件中導出數據:

I believe we have something like below to export data from a .sql file:

Invoke-Sqlcmd -ServerInstance ServerName -Database master -Username "user" -Password "pwd" -InputFile "C:\powershell\test.sql" | Out-File -FilePath "C:\powershell\test_(based on date).csv" 

但問題是將日期參數傳遞給 .sql 文件中的查詢.有人可以就如何實現這一點給我任何指示.

But the issue is passing date parameters to the query inside the .sql file. Could someone please give me any pointers on how to achieve this.

推薦答案

找到以下解決方案:

$DBScriptFile = "C:\Log\QUERY.sql"  #enter file path for .sql file
$outputpath="C:\log\" #Enter output path
$client="Client" # enter client name
$table="Table_name"
$sqlParameters =(("startdate='20191201'", "enddate='20191209'") ,("startdate='20191209'", "enddate='20191231'"),("startdate='20200101'", "enddate='20200201'"))
$count=1
foreach($i in $sqlParameters)
{
$filename=$outputpath+$client+"_"+$table +"_" + $count +".csv"

Invoke-Sqlcmd  ServerName -Database master -Username "user" -Password "pwd"   -InputFile $DBScriptFile -Variable $i |export-csv -path $filename -NoTypeInformation
$count=$count+1
}

我們需要添加 $(startdate) &$(enddate) 在 SQL 腳本文件中作為參數.

We need to add $(startdate) & $(enddate) in SQL script file for arguments.

這篇關于使用 powershell 導出 SQL 查詢輸出的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！