时间戳

首页 > 解决方案 > 使用 powershell 导出 SQL 查询输出

sql-server - 使用 powershell 导出 SQL 查询输出

问题描述

我对 Powershell 相当陌生，需要一些有关导出 SQL 服务器查询输出的指导。

我们有一个非常大的表，我们要从中导出数据。规模很大，因此将根据我们要提供的日期范围提取数据。因此，对于多个日期范围，我们将导出多个 csv 文件。作为其中的一部分，我们将使用日期范围在表之间运行一些查询，并且输出将被导出。我曾尝试使用 SQL Server 的内置功能 BCP，但输出不包含任何标题，格式也是一个问题。该表非常庞大，列数约为 500+，因此使用 UNION 显示标题不是使用 BCP 的选项。该查询将由不太熟悉 SQL 的人使用，因此也不推荐使用 GUI。因此想检查我们是否可以使用 powershell 来做到这一点。以下是我在 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"

但问题是将日期参数传递给 .sql 文件中的查询。有人可以给我任何关于如何实现这一目标的指示。

标签： sql-serverpowershelltsql

解决方案

不是最干净的解决方案，但应该足以满足需要 -

一种方法是创建一个带有输入参数的存储过程，并将日期参数作为 PowerShell 变量传递。

例如 -

CREATE PROCEDURE ExportDataFromLargeTable @date1 nchar(8),  @date2 nchar(8)
AS
DECLARE @ExportFolderName NVARCHAR(90);
DECLARE @ExportFileName NVARCHAR(90);
.
.
/*Rest of the code goes here*/
.
.
GO

创建 SP 后，您可以使用以下 PowerShell 代码执行上述过程 -

$Server = 'YourServerName'
$Database = 'YourDatabaseName'
$FirstDate = 'WhicheverFormatDate1IsIn'
$SecondDate = 'WhicheverFormatDate2IsIn'
$Query = "EXEC ExportDataFromLargeTable '$($FirstDate)', '$($SecondDate)'"
Invoke-Sqlcmd -Query $Query -ServerInstance $Server -Database $Database | Export-Csv "C:\powershell\test_(based on date).csv" -NoTypeInformation

passFirstDate后跟是SecondDate因为传递参数的顺序会影响输出。

推荐阅读