使用 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 文件中导出数据:

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" 

$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
}