使用 powershell 导出 SQL 查询输出 [英] Export SQL Query output using powershell

问题描述

我对 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 查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！