通过PowerShell向SQL Server插入数据：无报错但目标库无数据

问题描述 #

脚本意图从FileInformation表读取数据存入数组，再插入FileInformationReplicate表。脚本运行无报错，但目标表未写入任何数据。

原脚本代码 #

$Connection = Connect-SQLServer -InstanceName "Local\TestSchema" -DatabaseName "TestDB" -IntegratedSecurity $true

$Results = Invoke-SQLSelect -Connection $Connection -SelectStatement "Select * from [FileInformation]"

foreach ($i in $Results)
{
    $GetTest = $Results #| where-object {$_.id -eq 6}
    write-host "ID:"$i.id
    write-host "FolderPath:"$i.FolderPath
    Write-host "File:"$i.FileName
    Write-host "LastWriteT:"$i.LastWriteTime
    Write-host "CreateT:"$i.CreateTime
    Write-host "FileSZ:"$i.FileSizeinKB
}

$DataArray = @()
#adding to array

foreach ($i in $Results)
{
    $DataArrayLine = new-object PSObject
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "ID" -Value $i.id
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "FolderP" -Value $i.FolderPath
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "File" -Value $i.FileName
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "LastWriteT" -Value $i.LastWriteTime
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "CreateT" -Value $i.CreateTime
    $DataArrayLine | Add-Member -MemberType NoteProperty -Name "FileSZ" -Value $i.FileSizeinKB

    $Results += $DataArrayLine
}

$DataArray 

ForEach ($b in $DataArray)
{
    $id = $b.id -as [int]
    $FolderP = $b.FolderPath
    $File = $b.FileName
    $LastWriteT = $b.LastWriteTime -as [datetime]
    $CreateT = $b.CreateTime -as [datetime]
    $FileSZ = $b.FileSizeinKB

    Invoke-SQLInsert -Connection $Connection -InsertStatement "Insert Into FileInformationReplicate (FolderPath,Filename,LastWriteTime,CreateTime,FileSizeinKB) values ('$FolderP','$File',$LastWriteT,$CreateT,$FileSZ)" -Verbose
}

Close-SQLServerConnection -Connection $Connection

问题排查与修复 #

核心问题：目标数组未填充 #

原脚本中创建$DataArrayLine对象后，错误地将其添加到$Results数组而非$DataArray，导致$DataArray始终为空，后续插入循环根本不会执行。

其他潜在问题 #

1. 字符串拼接SQL语句存在SQL注入风险，且日期格式可能因系统区域设置导致插入失败。
2. 第一个foreach循环中的$GetTest = $Results属于冗余代码，无实际作用。

修正后的脚本 #

$Connection = Connect-SQLServer -InstanceName "Local\TestSchema" -DatabaseName "TestDB" -IntegratedSecurity $true

$Results = Invoke-SQLSelect -Connection $Connection -SelectStatement "Select * from [FileInformation]"

# 验证查询结果（可选，用于调试）
foreach ($i in $Results)
{
    write-host "ID:"$i.id
    write-host "FolderPath:"$i.FolderPath
    Write-host "File:"$i.FileName
    Write-host "LastWriteT:"$i.LastWriteTime
    Write-host "CreateT:"$i.CreateTime
    Write-host "FileSZ:"$i.FileSizeinKB
}

$DataArray = @()
foreach ($i in $Results)
{
    $DataArrayLine = [PSCustomObject]@{
        ID = $i.id
        FolderP = $i.FolderPath
        File = $i.FileName
        LastWriteT = $i.LastWriteTime
        CreateT = $i.CreateTime
        FileSZ = $i.FileSizeinKB
    }

    # 将对象添加到目标数组
    $DataArray += $DataArrayLine
}

# 使用参数化查询插入数据，避免SQL注入和格式问题
ForEach ($b in $DataArray)
{
    $insertQuery = @"
    INSERT INTO FileInformationReplicate (FolderPath, Filename, LastWriteTime, CreateTime, FileSizeinKB)
    VALUES (@FolderPath, @FileName, @LastWriteTime, @CreateTime, @FileSizeinKB)
"@
    $parameters = @{
        FolderPath = $b.FolderP
        FileName = $b.File
        LastWriteTime = $b.LastWriteT
        CreateTime = $b.CreateT
        FileSizeinKB = $b.FileSZ
    }

    Invoke-SQLInsert -Connection $Connection -InsertStatement $insertQuery -Parameters $parameters -Verbose
}

Close-SQLServerConnection -Connection $Connection

关键修改说明 #

• 将$Results += $DataArrayLine改为$DataArray += $DataArrayLine，确保目标数组被正确填充。
• 使用[PSCustomObject]更简洁地创建对象，替代New-Object和Add-Member。
• 改用参数化查询执行插入，避免SQL注入风险，同时保证日期、字符串等数据格式的正确性。
• 移除冗余的$GetTest = $Results代码。

内容的提问来源于stack exchange，提问作者Alvin