在数据库环境里PowerShell能否自动化执行复杂的SQL查询任务？

Import-Module SqlServer

# 建立数据库连接（建议将密码存储在安全凭证库）
$connStr = "Server=SQL01;Database=AdventureWorks;Integrated Security=True"

# 执行简单查询
$result = Invoke-Sqlcmd -ConnectionString $connStr -Query "
SELECT TOP 5 
    BusinessEntityID AS 员工编号,
    LoginID AS 系统账号,
    JobTitle AS 职位名称 
FROM HumanResources.Employee
WHERE Gender = 'M'"

# 结果转CSV并添加时间戳
$exportPath = "D:\Reports\Employee_$(Get-Date -Format 'yyyyMMdd').csv"
$result | Export-Csv -Path $exportPath -NoTypeInformation

# 安全传参方法
$departmentName = 'Engineering'

$safeQuery = @"
DECLARE @DeptName NVARCHAR(50) = @deptNameParam;
SELECT 
    e.BusinessEntityID,
    p.FirstName + ' ' + p.LastName AS FullName,
    e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory edh 
    ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department d 
    ON edh.DepartmentID = d.DepartmentID
WHERE d.Name = @DeptName
"@

$params = @{ deptNameParam = $departmentName }

Invoke-Sqlcmd -ConnectionString $connStr -Query $safeQuery -Variable $params

# 处理CSV文件入库
$csvData = Import-Csv -Path "D:\Data\NewEmployees.csv"

# 创建临时表
Invoke-Sqlcmd -ConnectionString $connStr -Query "
CREATE TABLE #TempEmployees (
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
)"

# 批量插入数据
$bulkQuery = @"
BULK INSERT #TempEmployees
FROM 'D:\Data\NewEmployees.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
)
"@
Invoke-Sqlcmd -ConnectionString $connStr -Query $bulkQuery

# 数据校验后正式入库
$finalQuery = @"
INSERT INTO HumanResources.Employee (BusinessEntityID, HireDate)
SELECT 
    p.BusinessEntityID,
    te.HireDate
FROM #TempEmployees te
JOIN Person.Person p 
    ON te.FirstName = p.FirstName 
    AND te.LastName = p.LastName
"@
Invoke-Sqlcmd -ConnectionString $connStr -Query $finalQuery

try {
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $transaction = $conn.BeginTransaction()

    # 更新操作1
    $cmd1 = New-Object System.Data.SqlClient.SqlCommand
    $cmd1.Connection = $conn
    $cmd1.Transaction = $transaction
    $cmd1.CommandText = "UPDATE Sales.SalesOrderHeader SET Status = 5 WHERE AccountNumber = 'AW000123'"
    $cmd1.ExecuteNonQuery()

    # 更新操作2
    $cmd2 = New-Object System.Data.SqlClient.SqlCommand
    $cmd2.Connection = $conn
    $cmd2.Transaction = $transaction
    $cmd2.CommandText = "INSERT INTO Sales.SalesLog (LogDate, Action) VALUES (GETDATE(), '订单状态更新')"
    $cmd2.ExecuteNonQuery()

    $transaction.Commit()
}
catch {
    $transaction.Rollback()
    Write-Error "事务回滚：$_"
}
finally {
    $conn.Close()
}

$queryConfig = @{
    ConnectionString = $connStr
    Query = "EXEC LongRunningProcedure"
    QueryTimeout = 600  # 10分钟超时
    ErrorAction = 'Stop'
}

try {
    Invoke-Sqlcmd @queryConfig
}
catch {
    if ($_.Exception -like "*超时*") {
        Send-MailMessage -To "dba@company.com" -Subject "查询超时警报" -Body $queryConfig.Query
    }
}